Re: Aggregates (last/first) not behaving

On 22/07/10 07:37, Wes Devauld wrote:
> I have PostgreSQL 8.3.9 [PostgreSQL 8.3.9 on i386-apple-darwin10.3.0,
> compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc.
> build 5646) (dot 1)]
>
> and the custom first and last aggregates from:
> http://wiki.postgresql.org/wiki/First_(aggregate)
> http://wiki.postgresql.org/wiki/Last_(aggregate)
>
> I have a simple table, of two columns.  The first is a timestamp and is
> the primary key, the second is an integer.  I've loaded the table up
> with values, one for every minute, for a whole year.  Some SQL to
> recreate the table and the aggregates can be retrieved from:
>
> http://blog.devauld.ca/aggregate_test.zip (File is approximately 180KB)
>
> Now when I try to make use of the first and last aggregates, I get:
>
> # select first(t), last(t) from test group by extract(day from t);
>          first        |        last
> ---------------------+---------------------
>   2009-01-01 00:00:00 | 2009-01-01 17:02:00
>   2009-01-02 10:07:00 | 2009-01-02 10:06:00
>   2009-01-03 20:15:00 | 2009-01-03 20:14:00
[snip]
> For some reason the aggregates are not falling into the proper group.  I
> can't blame timezones as the results are all over the map, and
> first/last relationship is broken as in some cases 'last' is
> chronologically before 'first'

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