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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
