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
WHERE date_trunc( 'hour', "when" )::timestamp = ?
ORDER BY "when" DESC
But, I figure there's probably some what to do this in SQL.
21:12:24 up 21 days, 11:00, 4 users, load average: 2.23, 1.69, 1.28
pgsql-sql by date
|Next:||From: Tom Lane||Date: 2004-01-19 04:54:46|
|Subject: Re: help with limiting query results |
|Previous:||From: Tom Lane||Date: 2004-01-19 04:10:29|
|Subject: Re: Problem with LEFT JOIN |