Re: using window-functions to get freshest value - how? - Mailing list pgsql-general

Massa, Harald Armin, 20.11.2009 11:07:
> 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:

Not tested:

SELECT id_bf,
       wert,
       max(letztespeicherung) over (partition by id_bf)
FROM rfmitzeit

Regards
Thomas