Date: Sun, 18 Jan 2004 21:28:34 -0700
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?
Message-Id: <20040118212834.7c667b9b.Robert_Creager@LogicalChaos.org>
Organization: Starlight Vision, LLC.
Mime-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg="pgp-sha1";
	boundary="Multipart_Sun__18_Jan_2004_21_28_34_-0700_zC6AP4pLu=.PCz0a"

--Multipart_Sun__18_Jan_2004_21_28_34_-0700_zC6AP4pLu=.PCz0a
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit


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

--Multipart_Sun__18_Jan_2004_21_28_34_-0700_zC6AP4pLu=.PCz0a
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAkALXPgACgkQLQ/DKuwDYzlGBwCfTNTRd3fuFYxwDDR76L4WACrC
JnkAmgJfzt/XSKDHJzCO68zlXsoGBxjY
=LWXD
-----END PGP SIGNATURE-----

--Multipart_Sun__18_Jan_2004_21_28_34_-0700_zC6AP4pLu=.PCz0a--