oops, I forgot the partition by. Here's the correct query: SELECT * FROM (SELECT *,row_number() OVER (PARTITION BY id_rf ORDER BY letztespeicherung DESC) FROM rfmitzeit) t WHERE row_number=1 You can also do it using SELECT DISTINCT ON: SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf, letztespeicherung DESC; My guess is that the latter will perform better but you should do your own testing. On Fri, Nov 20, 2009 at 5:36 AM, silly8888 <silly8888@xxxxxxxxx> wrote: > SELECT * FROM (SELECT *,row_number() OVER (ORDER BY letztespeicherung > DESC) FROM rfmitzeit) t WHERE row_number=1 > > > > On Fri, Nov 20, 2009 at 5:31 AM, Massa, Harald Armin <chef@xxxxxxx> wrote: >> id_bf, wert, letztespeicherung: >>>> >>>> 98, 'blue', 2009-11-09 >>>> 98, 'red', 2009-11-10 >>>> now I have a select to get the "youngest value" for every id_bf: >>> >>> Not tested: >>> >>> SELECT id_bf, wert, >>> max(letztespeicherung) over (partition by id_bf) >>> FROM rfmitzeit >> >> no, that does not work: >> "id_bf";"wert";"max" >> 98;"blue";"2009-11-10 00:00:00" >> 98;"red";"2009-11-10 00:00:00" >> >> result is: I get the date of the youngest value. >> >> My expected result is: >> >> 98;"red" >> >> (that is, the entry of "wert" that is youngest) >> >> thanks for trying, >> >> Harald >> >> -- >> GHUM Harald Massa >> persuadere et programmare >> Harald Armin Massa >> Spielberger Straße 49 >> 70435 Stuttgart >> 0173/9409607 >> no fx, no carrier pigeon >> - >> %s is too gigantic of an industry to bend to the whims of reality >> > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
