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