Aggregates puzzle

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

Responses

Browse pgsql-sql by date

  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