Re: Ask About SQL

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