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@gmail.com> 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@ghum.de> 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
>>
>
