using window-functions to get freshest value - how?

Поиск
Список
Период
Сортировка
От Massa, Harald Armin
Тема using window-functions to get freshest value - how?
Дата
Msg-id e3e180dc0911200207j2882e2deo9eb9f2ca2236c2ce@mail.gmail.com
обсуждение исходный текст
Ответы Re: using window-functions to get freshest value - how?  (Thomas Kellerer <spam_eater@gmx.net>)
Re: using window-functions to get freshest value - how?  (hubert depesz lubaczewski <depesz@depesz.com>)
Список pgsql-general
I have a table

CREATE TABLE rfmitzeit
(
 id_rf inet NOT NULL,
 id_bf integer,
 wert text,
 letztespeicherung timestamp without time zone
 CONSTRAINT repofeld_id_rf PRIMARY KEY (id_rf),
 );
 
 
where for one id_bf there are stored mutliple values ("wert") at multiple dates:
   
   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:
   
select
rfmitzeit.id_bf, rfmitzeit.wert
   from
rfmitzeit
join
(select
   id_bf, max(rfmitzeit.letztespeicherung) as maxsi
from
   rfmitzeit
group by id_bf
) idbfzeit on (rfmitzeit.id_bf=idbfzeit.id_bf and rfmitzeit.letztespeicherung=idbfzeit.maxsi)  

which works quite fine. But I want to extend my knowledge....

so I have the feeling that this should be possible using a WINDOW-function,
but I can not find out how. (besides curiousity, I also want to test if doing this kind of query with window-functions will be faster)

Is it possible? How would the SQL utilizing WINDOW-functions look like?

Best wishes,

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

В списке pgsql-general по дате отправления:

Предыдущее
От: "A. Kretschmer"
Дата:
Сообщение: Re: Allowing for longer table names (>64 characters)
Следующее
От: Tino Wildenhain
Дата:
Сообщение: Re: Allowing for longer table names (>64 characters)