Re: how to do this select?

In response to Yi Zhao :
> ok, thanks, I will create a new message when I post next time.
>
> about my question, I think distinct can't solve my problem, because I
> want to get more than one rows. if there is more than (or equal) 2 (eg:
> 2, 3, 4, 100 ...)rows have the same value of column 'b' , I want to get
> only 2 rows. if lesse than 2, I want get all the result of them.
>
> ps: I' think, the *2* in my example is not appropriate, how about 10,
> 50?

Okay, as Craig Ringer said, you can use new features in 8.4. For example:

test=# select * from foo;
a | b
---+---
a | a
b | a
c | a
d | a
a | b
b | b
c | b
a | c
a | d
a | e
b | e
c | e
d | e
(13 rows)

So, now i'm counting the rows, partition by b:

test=# select b, a, row_number() over (partition by b) from foo order by b, a;
b | a | row_number
---+---+------------
a | a | 1
a | b | 2
a | c | 3
a | d | 4
b | a | 1
b | b | 2
b | c | 3
c | a | 1
d | a | 1
e | a | 1
e | b | 2
e | c | 3
e | d | 4
(13 rows)

Next step, only up to 2 entries for every value in b:

test=# select * from (select b, a, row_number() over (partition by b) from foo order by b, a) temp where row_number <= 2;
b | a | row_number
---+---+------------
a | a | 1
a | b | 2
b | a | 1
b | b | 2
c | a | 1
d | a | 1
e | a | 1
e | b | 2
(8 rows)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net