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

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

How can I get the last element out of GROUP BY sets?
5+ messages / 4 participants
[nested] [flat]

* How can I get the last element out of GROUP BY sets?
@ 2004-01-19 04:28  Robert Creager <Robert_Creager@LogicalChaos.org>
  0 siblings, 2 replies; 5+ messages in thread

From: Robert Creager @ 2004-01-19 04:28 UTC (permalink / raw)
  To: 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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How can I get the last element out of GROUP BY sets?
@ 2004-01-19 05:44  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Robert Creager <Robert_Creager@LogicalChaos.org>
  1 sibling, 2 replies; 5+ messages in thread

From: Tom Lane @ 2004-01-19 05:44 UTC (permalink / raw)
  To: Robert Creager <Robert_Creager@LogicalChaos.org>; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How can I get the last element out of GROUP BY sets?
@ 2004-01-19 11:12  Christoph Haller <ch@rodos.fzk.de>
  parent: Robert Creager <Robert_Creager@LogicalChaos.org>
  1 sibling, 0 replies; 5+ messages in thread

From: Christoph Haller @ 2004-01-19 11:12 UTC (permalink / raw)
  To: Robert_Creager@LogicalChaos.org; +Cc: 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
> 
Looks like "SELECT DISTINCT ON" is your friend. RTFM. 

HTH

Regards, Christoph 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How can I get the last element out of GROUP BY sets?
@ 2004-01-19 17:38  Robert Creager <Robert_Creager@LogicalChaos.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Robert Creager @ 2004-01-19 17:38 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-sql

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
      ORDER BY 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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How can I get the last element out of GROUP BY sets?
@ 2004-01-23 02:23  Greg Stark <gsstark@mit.edu>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Greg Stark @ 2004-01-23 02:23 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Creager <Robert_Creager@LogicalChaos.org>; pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

end of thread, other threads:[~2004-01-23 02:23 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-19 04:28 How can I get the last element out of GROUP BY sets? Robert Creager <Robert_Creager@LogicalChaos.org>
2004-01-19 05:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-19 17:38   ` Robert Creager <Robert_Creager@LogicalChaos.org>
2004-01-23 02:23   ` Greg Stark <gsstark@mit.edu>
2004-01-19 11:12 ` Christoph Haller <ch@rodos.fzk.de>

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