From: Tim Landscheidt <tim@tim-landscheidt.de>
To: pgsql-sql@postgresql.org
Subject: Re: Aggregates (last/first) not behaving
Date: Thu, 22 Jul 2010 18:42:14 +0000
Message-ID: <m3eievtl2x.fsf@passepartout.tim-landscheidt.de> (raw)
References: <AANLkTim1g8cejgRokrpTr6a_z6bXeKxOhUSFeTIwMrcK@mail.gmail.com>
<4C480508.8080306@archonet.com>
<AANLkTinkjZLrYsDNnKpvShhu-U5-OAStISy1XPSUqegx@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tim@tim-landscheidt.de
Subject: Re: Aggregates (last/first) not behaving
In-Reply-To: <m3eievtl2x.fsf@passepartout.tim-landscheidt.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox