Re: Aggregates (last/first) not behaving

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(at)archonet(dot)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
>