Re: Select first ten of each category? - Mailing list pgsql-general

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