I've been working on this problem in vain for several days now. I
essentially am looking for one row per hour where that row is the youngest
row of that hour.
So if there are say three rows labeled (17:00, 17:41, 17:43) I only want
17:43's row to show for the 17th hour. Essentially I want to see what I
have in the table below but just with out the red rows (if you cant see the
red, its the 17:00 and 17:41 rows). How can I fix or rewrite this select
statement to get what I want.
I've looked at using group by/having, correlated subselects, between. I can
get a union of 24 correlated subselects to work, but its way too slow for
what I'd need.
SELECT hourlycounter, extract(hour from utcissuetime) as hour,
extract(minute from utcissuetime) as minute, CumulativePrecipitation,
FROM observations, awos
and hourlyidentifier = 201
and utcissuedate = '20031006'
and utcissuetime >= '00:00:00'
and utcissuetime <= '19:00:00'
and observations.obsid = awos.obsid
ORDER BY hour DESC, minute DESC;