Try this: select * from flood_ts F join (SELECT lid, MAX(value) AS mvalue, event_id FROM flood_ts GROUP BY lid, event_id) X on F.lid = X.lid and value = mvalue and X.event_id = F.event_id
01.04.2011, 00:15, "Mark Fenbers" <mark.fenb...@noaa.gov>: > 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
