Re: using window-functions to get freshest value - how?

Thom, depesz, silly,

> SELECT DISTINCT ON (id_bf) id_bf, wert, letztespeicherung
> FROM rfmitzeit
> ORDER BY id_bf, letztespeicherung ASC;

yes, that does work. I put it in the real world query (my example was
reduced to the relevant parts), and it provides an impressive speedup
(down from 2234 to 1600ms on the worst possible dataset with worst
possible disk/cache situation)

And I learned that

DISTINCT ON (xxx) .... order by zzzz

will first do the order by, and then definitely take every "first row"
of the ordered result.

My expection of DISTINCT on (id_bf) was "give me ONLY ONE of the rows
with every id_bf", I was not aware of the fact that it will give me
THE FIRST of the 'ordered by' result set.

Thanks to all who helped me learn today!

Harald

(and sorry for leaving HTML on on some of the mails)

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