Skip site navigation (1) Skip section navigation (2)

How can I get the last element out of GROUP BY sets?

From: Robert Creager <Robert_Creager(at)LogicalChaos(dot)org>
To: PGSQL <pgsql-sql(at)postgresql(dot)org>
Subject: How can I get the last element out of GROUP BY sets?
Date: 2004-01-19 04:28:34
Message-ID: 20040118212834.7c667b9b.Robert_Creager@LogicalChaos.org (view raw or flat)
Thread:
Lists: pgsql-sql
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

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-01-19 04:54:46
Subject: Re: help with limiting query results
Previous:From: Tom LaneDate: 2004-01-19 04:10:29
Subject: Re: Problem with LEFT JOIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group