Thread: Ask About SQL
<span style="color: rgb(51, 51, 255);">Hi All.</span><br style="color: rgb(51, 51, 255);" /><br style="color: rgb(51, 51,255);" /><span style="color: rgb(51, 51, 255);">Can help to create sql queries for this data :</span><br style="color:rgb(51, 51, 255);" /><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(255, 0, 0);">tabel A</span><brstyle="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0, 0);">field1, field2, field3</span><br style="color:rgb(255, 0, 0);" /><span style="color: rgb(255, 0, 0);">x1, y1, 5</span><br style="color: rgb(255, 0,0);" /><span style="color: rgb(255, 0, 0);">x1, y2, 1</span><br style="color: rgb(255, 0, 0);" /><span style="color:rgb(255, 0, 0);">x2, y1, 2</span><br style="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0,0);">x2, y3, 4</span><br style="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0, 0);">x1, y3, 4</span><br style="color: rgb(51, 51, 255);" /><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51,51, 255);">I want to get 2 record with the max value at field3 for each kombination of field1 :</span><br style="color:rgb(51, 51, 255);" /><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(255, 0, 0);">tabel B</span><brstyle="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0, 0);">field1, field2, field3</span><br style="color:rgb(255, 0, 0);" /><span style="color: rgb(255, 0, 0);"> x1, y1, 5</span><br style="color: rgb(255,0, 0);" /><span style="color: rgb(255, 0, 0);"> x1, y3, 4</span><br style="color: rgb(255, 0, 0);" /><spanstyle="color: rgb(255, 0, 0);"> x2, y3, 4</span><br style="color: rgb(255, 0, 0);" /><span style="color: rgb(255,0, 0);"> x2, y1, 2</span><br style="color: rgb(51, 51, 255);" /><br style="color: rgb(51, 51, 255);" /><spanstyle="color: rgb(51, 51, 255);">Anyone have an ideas?</span><br style="color: rgb(51, 51, 255);" /><span style="color:rgb(51, 51, 255);">Thanks anyway.</span><br clear="all" /><br />-- <br />-------------------------------------------------------------------<br/> "He who is quick to become angry will commit folly,and a crafty man is hated"<br />
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 | 5x1 | y2 | 1x2 | y1 | 2x2 | y3 | 4x1 | 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 | 5x1 | y3 | 4x2 | y1 | 2x2 | 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
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 | 5x1 | y2 | 1x1 | y2 | 3x2 | y3 | 2x2 | 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 | 5x1 | y2 | 3x2 | y3 | 4 (3 rows) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com