Try this. Once you know the value you want you have to join back to find the time of that value.
Select f.Lid, F.Value,F.event_id, f.obstime From ( SELECT lid, MAX(value) As Value, event_id FROM flood_ts GROUP BY lid, event_id )sub Join flood f On sub.Value=f.value and f.lid=sub.lid and sub.event_id=v.event_id -----Original Message----- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Mark Fenbers Sent: Thursday, March 31, 2011 1:15 PM To: PostgreSQL - SQL Subject: [SQL] Aggregates puzzle SQL gurus, I have a table with 4 columns: lid(varchar), value(float), obstime(datetime), event_id(integer) I want to find the MAX(value) and the time and date that it occurred (obstime) in each group of rows where the lid and event_id are the same. What I have works correctly in identifying the MAX(value) for the given group, but I'm having trouble getting the corresponding obstime to be reported along with it. Here's the SQL I have: SELECT lid, MAX(value), event_id FROM flood_ts GROUP BY lid, event_id ORDER BY lid; If I add "obstime" to the SELECT list, then I need to add "value" to the GROUP BY clause, which makes the MAX(value) function report *each row* as a maximum. So, how can I revise my SQL to report the obstime that the MAX(value) occurred? Any help is sincerely appreciated. Mark -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
