Re: Aggregates puzzle

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(dot)Fenbers(at)noaa(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql