Hello Terry,
Thanks a lot. That's so simple I didn't see it. (The original query is
much more complex.)
The only problem is, rank is not a column of category itself, but a
joined row. With this solution, the join will have to be performed
twice. But since this doesn't cost that much and because the second join
is only done for 5 rows at the max this does not hurt.
The more complete query now looks a little ugly:
SELECT id, get_category_text_path(id), r.rank
FROM category
JOIN rank_lookup AS r ON cat_id = id
WHERE id IN (
SELECT c.id
FROM category AS c
JOIN rank_lookup AS rr ON rr.cat_id = c.id
ORDER BY rr.rank
LIMIT 5
)
It's not possible to optimize out that second join, is it?
Regards
Markus
On Tue, 2006-05-02 at 07:39 -0400, Terry Fielder wrote:
> SELECT id, get_category_text_path(id)
> FROM category
> WHERE id IN (
> SELECT c.id
> FROM category AS c
> ORDER BY c.rank
> LIMIT 5
> )
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
