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
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) |