Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: Aggregates (last/first) not behaving
Date: Thu, 22 Jul 2010 18:42:14 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 42
Message-ID: <m3eievtl2x.fsf@passepartout.tim-landscheidt.de>
References: <AANLkTim1g8cejgRokrpTr6a_z6bXeKxOhUSFeTIwMrcK@mail.gmail.com>
	<4C480508.8080306@archonet.com>
	<AANLkTinkjZLrYsDNnKpvShhu-U5-OAStISy1XPSUqegx@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:aug4Q+IBiSQ93JBmQfESs/0zSUk=

Wes Devauld <wes@devauld.ca> wrote:

> 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.
> [...]

Perhaps you could /concisely/ rephrase your problem. Finding
the first/last value per group with/without window functions
is a common problem, and there are lots of solutions to it.
But few people will wade through lots of text to find out
what's bothering you.

  For example, you can query the "last" values per day along
the lines of (untested):

| SELECT EXTRACT(day FROM t), v
|   FROM test
|   WHERE t IN (SELECT MAX(t) FROM test
|                 GROUP BY EXTRACT(day FROM t));

Obviously, this doesn't "keep using last()", so I don't know
whether it's good or bad for you.

Tim