using window-functions to get freshest value - how?

From: "Massa, Harald Armin" <chef(at)ghum(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: using window-functions to get freshest value - how?
Date: 2009-11-20 10:07:37
Message-ID: e3e180dc0911200207j2882e2deo9eb9f2ca2236c2ce@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tino Wildenhain 2009-11-20 10:19:37 Re: Allowing for longer table names (>64 characters)
Previous Message A. Kretschmer 2009-11-20 09:54:20 Re: Allowing for longer table names (>64 characters)