Re: Select first ten of each category?

On Apr 13, 2006, at 10:16 , Benjamin Smith 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?

You can use a correlated subquery, something like (untested):

select category, id, name, price
     , (
         select count(*)
         from items i2
         where i2.category = i.category
             and i2.price > i.price
         ) + 1 as rank
from items i
order by category, rank desc;

Note that this query actually counts the number of items (in the
category) with prices greater than the given item's price.

And to limit it just to the top 10 items:

select category, id, name, price, rank
from (
     select category, id, name, price
         , (
             select count(*)
             from items i2
             where i2.category = i.category
                 and i2.price > i.price
             ) + 1 as rank
     from items i
)
where rank <= 10
order by category, rank desc;

Note that this may return more than 10 items per category in the case
of more than one item in a given category having the same price.

Hope this points you in the right direction.

Michael Glaesemann
grzm myrealbox com