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?
Re: using window-functions to get freshest value - how? |
Список | 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
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 по дате отправления: