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

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

Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> ... one piece of data I need is the last value for each GROUP BY
> period.  Alas, I cannot figure out how to do this.

SELECT DISTINCT ON (rather than GROUP BY) could get this done for you.
        regards, tom lane

> 
> 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
> 
Looks like "SELECT DISTINCT ON" is your friend. RTFM. 

HTH

Regards, Christoph 

When grilled further on (Mon, 19 Jan 2004 00:44:30 -0500),
Tom Lane <tgl@sss.pgh.pa.us> confessed:

> Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> > ... one piece of data I need is the last value for each GROUP BY
> > period.  Alas, I cannot figure out how to do this.
> 
> SELECT DISTINCT ON (rather than GROUP BY) could get this done for you.
> 

I had my whine all ready as to how I still couldn't figure it out, when I
figured it out:

...
SELECT p.period, etday
FROM (SELECT DISTINCT ON ( period )        date_trunc( 'hour', "when" ) AS period,        etday     FROM readings
ORDERBY period, "when" DESC) AS p
 
...

Thanks for the tip.

Cheers,
Rob

-- 08:10:55 up 21 days, 21:58,  4 users,  load average: 2.15, 2.06, 2.02

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> > ... one piece of data I need is the last value for each GROUP BY
> > period.  Alas, I cannot figure out how to do this.
> 
> SELECT DISTINCT ON (rather than GROUP BY) could get this done for you.

Or if you need to combine this with other aggregate functions like sum, count,
etc:

CREATE FUNCTION first_accum (integer, integer) RETURNS integer AS 'select coalesce($1,$2)' LANGUAGE sql;
CREATE FUNCTION last_accum (integer, integer) RETURNS integer AS 'select $2' LANGUAGE sql;
CREATE AGGREGATE first (BASETYPE = integer, SFUNC = first_accum, STYPE = integer);
CREATE AGGREGATE last (BASETYPE = integer, SFUNC = last_accum, STYPE = integer);

Then you can do first() and last(). These definitions only work for integer
but you can pattern match for other datatypes. You might be able to get a
universal function working using anyelement now, I haven't tried.

-- 
greg