From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: PGSQL <pgsql-sql@postgresql.org>
Subject: How can I get the last element out of GROUP BY sets?
Date: Sun, 18 Jan 2004 21:28:34 -0700
Message-ID: <20040118212834.7c667b9b.Robert_Creager@LogicalChaos.org> (raw)
I'm trying to produce summary data from a table (using PGSQL 7.4.1):
CREATE TABLE readings( "when" timestamp, value integer );
The summary will be based on various time periods. I've been using date_trunc(
'hour', "when" ) and GROUP BY for the min/max/average readings with no problems.
But, one piece of data I need is the last value for each GROUP BY period. Alas,
I cannot figure out how to do this.
If I wanted to loop from a script, I could, for instance, execute the following
for each GROUP BY period (filling in ? appropriately):
SELECT date_trunc( 'hour', "when" ), value
FROM readings
WHERE date_trunc( 'hour', "when" )::timestamp = ?
ORDER BY "when" DESC
LIMIT 1
But, I figure there's probably some what to do this in SQL.
Any help?
Thanks,
Rob
--
21:12:24 up 21 days, 11:00, 4 users, load average: 2.23, 1.69, 1.28
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: Robert_Creager@LogicalChaos.org
Subject: Re: How can I get the last element out of GROUP BY sets?
In-Reply-To: <20040118212834.7c667b9b.Robert_Creager@LogicalChaos.org>
* 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