Hello,
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.
Thanks inadvance for any help anyone can give.
- SQL Man
-----------------------------------------------------
SELECT hourlycounter, extract(hour from utcissuetime) as hour,
extract(minute from utcissuetime) as minute, CumulativePrecipitation,
OneHourPrecipitation
FROM observations, awos
WHERE stationid='YIO'
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;
hourlycounter | hour | minute | cumulativeprecipitation |
onehourprecipitation
---------------+------+--------+-------------------------+----------------
------
0 | 19 | 0 | 46.5 |
0.5
0 | 18 | 0 | 46 |
0.5
3 | 17 | 43 | 78 |
2 | 17 | 41 | 78 |
0 | 17 | 0 | 45.5 |
0.5
0 | 16 | 0 | 45 |
0
0 | 15 | 0 | 45 |
0.5
0 | 14 | 0 | 44.5 |
0
0 | 13 | 0 | 44.5 |
0
0 | 12 | 0 | 44.5 |
0
0 | 11 | 0 | 44.5 |
0.5
0 | 10 | 0 | 44 |
0.5
0 | 9 | 0 | 43.5 |
0
0 | 8 | 0 | 43.5 |
0
0 | 7 | 0 | 43.5 |
0
0 | 6 | 0 | 43.5 |
0
0 | 5 | 0 | 43.5 |
0
0 | 4 | 0 | 43.5 |
0
0 | 3 | 0 | 43.5 |
0
0 | 2 | 0 | 43.5 |
0
0 | 1 | 0 | 43.5 |
0
0 | 0 | 0 | 43.5 |
0
(22 rows)