Thread: Select first ten of each category?

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...

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

Benjamin Smith wrote:
> 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?

Use a LIMIT on your SELECT. See:
http://www.postgresql.org/docs/8.1/static/queries-limit.html

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

On Apr 13, 2006, at 11:08 , Yanni Chiu wrote:

> Benjamin Smith wrote:
>> 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?
>
> Use a LIMIT on your SELECT. See:
> http://www.postgresql.org/docs/8.1/static/queries-limit.html

Without using UNION, (which would require writing a select statement
for each category), how would LIMIT allow him to do this for each
category in a single query?


Michael Glaesemann
grzm myrealbox com

Michael Glaesemann wrote:
> Without using UNION, (which would require writing a select statement
> for each category), how would LIMIT allow him to do this for each
> category in a single query?

You're right, it would need a UNION, and a SELECT per category.
So there'd be another SELECT to find all the categories beforehand.

On Wed, 12 Apr 2006, 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));



I think this should work....

select * from items
order by price desc
limit 10;


Cheers,

  Brent Wood

>
> 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...
>