Re: using window-functions to get freshest value - how?

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(at)gmail(dot)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(at)ghum(dot)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
>>
>