From: | Mark Fenbers <Mark(dot)Fenbers(at)noaa(dot)gov> |
---|---|
To: | PostgreSQL - SQL <pgsql-sql(at)postgresql(dot)org> |
Subject: | Aggregates puzzle |
Date: | 2011-03-31 20:15:07 |
Message-ID: | 4D94E0CB.4020309@noaa.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
Attachment | Content-Type | Size |
---|---|---|
Mark_Fenbers.vcf | text/x-vcard | 409 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Ozer, Pam | 2011-03-31 23:14:59 | Re: Aggregates puzzle |
Previous Message | Jasen Betts | 2011-03-27 08:32:52 | Re: converting big int to date |