Search Postgresql Archives

using window-functions to get freshest value - how?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux