On Wed, Aug 19, 2009 at 05:17:07PM +0700, Otniel Michael wrote: > 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? > Thanks anyway. > > -- > ------------------------------------------------------------------- > "He who is quick to become angry will commit folly, and a crafty man is > hated"
Your example doesn't match your description (the combination of x1 and y2 isn't listed). However, from your description it looks like what you want is DISTINCT ON http://www.postgresql.org/docs/8.4/interactive/sql-select.html#SQL-DISTINCT Something like this: SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM A ORDER BY field1, field2, field3; Here's an example. # select * from b order by f1, f2, f3; f1 | f2 | f3 ----+----+---- x1 | y1 | 5 x1 | y2 | 1 x1 | y2 | 3 x2 | y3 | 2 x2 | y3 | 4 (5 rows) # select distinct on (f1, f2) f1, f2, f3 from b order by 1, 2, 3 desc; f1 | f2 | f3 ----+----+---- x1 | y1 | 5 x1 | y2 | 3 x2 | y3 | 4 (3 rows) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com
signature.asc
Description: Digital signature
