On 4/13/06,
Benjamin Smith <
lists@benjamindsmith.com> wrote:
I'm stumped on this one...
I have a table defined thusly:
create table items (
id serial,
category integer not null references category(id),
name varchar not null,
price real,
unique(category, name));
It has a LARGE number of entries. I'd like to grab the 10 most expensive items
from each category in a single query. How can this be done? Something like
Select items.*
FROM items
where id IN (
select firstTen(id) FROM items
group by category
ORDER BY price DESC
)
ORDER BY price desc;
But I've not found any incantation to make this idea work...
I came up with something like this:
SELECT i.* FROM items i JOIN (
SELECT category, (
SELECT price FROM items ii
WHERE ii.category=io.category
ORDER BY price DESC OFFSET 9 LIMIT 1
) AS date FROM items io GROUP BY category) AS sel
ON (i.category=sel.category AND i.price >= sel.price);
I,e. First do a select which will return ninth price of each
category (two inner selects) and then JOIN it with a whole
table, where category and price match they way you want.
Keep in mind, if you happen to have same prices at position
near 10th, you'll end up having more than 10 returns per
given category. Either filter it at application level, or embed
some additional key inside the join condition (like
ON (i.category=sel.category AND (i.price > sel.price OR
(i.price=sel.price AND i.id=sel.last_id)));
Regards,
Dawid