Re: select top N entries from several groups

On Apr 12, 2005, at 7:21 AM, David Orme wrote:

> Hi,
>
> Suppose I have a table (called temp) like this:
>
> gp val
> A 10
> A 8
> A 6
> A 4
> B 3
> B 2
> B 1
> B 0
>
> How can I get the largest two values for each group in a single pass?
> I want to end up with:
>
> gp val
> A 10
> A 8
> B 3
> B 2
>
> I can do this a group at a time using...

How about:

create table temp (
gp char,
val int
);

insert into temp values ('A',10);
insert into temp values ('A',8);
insert into temp values ('A',6);
insert into temp values ('A',4);
insert into temp values ('B',3);
insert into temp values ('B',2);
insert into temp values ('B',1);

select a.gp,a.val
from temp a
where a.val in (
select b.val
from temp b
where a.gp=b.gp
order by b.val desc
limit 2);

gp | val
----+-----
A | 10
A | 8
B | 3
B | 2
(4 rows)

I have found this link is useful for beginning to think about
subqueries:

http://www.postgresql.org/files/documentation/books/aw_pgsql/node81.html

Sean