From: | Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz> |
---|---|
To: | Benjamin Smith <lists(at)benjamindsmith(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Select first ten of each category? |
Date: | 2006-04-13 02:12:04 |
Message-ID: | 20060413140948.G76831@storm-user.niwa.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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...
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2006-04-13 02:16:59 | Re: Select first ten of each category? |
Previous Message | Michael Glaesemann | 2006-04-13 02:11:26 | Re: Select first ten of each category? |