I believe I lost the flavour of what I'm doing when I constructed this example. I'm not interested in the timepoint as much as the value that is attached to it. I need to be able to find the last chronological record for a given day.
I can get the value for which I am looking in two steps:
select max(t) as t into table last_of_day from test group by extract(day from t);
select last_of_day.t, test.v from last_of_day, test where last_of_day.t = test.t;
I was fairly happy when first() and last() were discovered, as these two steps could be merged, until the table grows too large and the query planner decides to sort the results before they are aggregated.
I was searching for a way to keep using last() and keeping the extraction to a single step, although the more I fight with it, the less I think that it is worth it. If you have any further suggestions, I would appreciate hearing them.
-W
On Thu, Jul 22, 2010 at 2:44 AM, Richard Huxton
<dev@archonet.com> wrote:
They all seem grouped properly (by day) to me. Unless I've missed something.
The first/last aggregates aren't ordered in any way. They are "first value I happened to find" and "last value I happened to find".
If you want the earliest/latest timestamp from each day, use min() and max().
--
Richard Huxton
Archonet Ltd