Re: Select first ten of each category?

On 4/13/06, Benjamin Smith <lists(at)benjamindsmith(dot)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