Re: max timestamp

When grilled further on (10 Feb 2004 10:14:04 -0800),
stermic@gw.co.jackson.mo.us (Michael Sterling) confessed:

> i'm trying to get the max time stamp, from each day, of a range of
> dates, not just the max time stamp for the complete range dates but
> for each day.
> 

Well, one gross and ugly way is:

SELECT MAX( "when" ) FROM readings, (SELECT DATE_TRUNC( 'day', "when" ) AS
period FROM readings GROUP BY period) AS p WHERE DATE_TRUNC( 'day', "when" ) =
p.period GROUP BY p.period;

given the table readings looks something like:

CREATE TABLE readings  (  "when"              TIMESTAMP DEFAULT timeofday()::timestamp                         NOT NULL
PRIMARYKEY  );
 

I'm curious to see other, 'better' solutions.

Cheers,
Rob

-- 12:52:57 up 20:36,  2 users,  load average: 2.08, 2.17, 2.18
Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003