In response to Otniel Michael : > Hi All. > > Can help to create sql queries for this data : > > tabel A > field1, field2, field3 > x1, y1, 5 > x1, y2, 1 > x2, y1, 2 > x2, y3, 4 > x1, y3, 4 > > I want to get 2 record with the max value at field3 for each kombination of > field1 : > > tabel B > field1, field2, field3 > x1, y1, 5 > x1, y3, 4 > x2, y3, 4 > x2, y1, 2 > > Anyone have an ideas?
Works since 8.4: test=*# select * from table_a ; field1 | field2 | field3 --------+--------+-------- x1 | y1 | 5 x1 | y2 | 1 x2 | y1 | 2 x2 | y3 | 4 x1 | y3 | 4 (5 rows) test=*# select field1, field2, field3 from (select field1, field2, field3, row_number() over(partition by field1 order by field3 desc) from table_a order by field1, field3) foo where row_number < 3 order by field1, field2; field1 | field2 | field3 --------+--------+-------- x1 | y1 | 5 x1 | y3 | 4 x2 | y1 | 2 x2 | y3 | 4 (4 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-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
