Date: Thu, 31 Mar 2011 16:15:07 -0400
From: Mark Fenbers <Mark.Fenbers@noaa.gov>
Subject: Aggregates puzzle
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Message-id: <4D94E0CB.4020309@noaa.gov>
Organization: DOC/NOAA/NWS/OHRFC
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_Wi9a5wxpHeyLCFoxAhUgRw)"
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15)
	Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9

This is a multi-part message in MIME format.

--Boundary_(ID_Wi9a5wxpHeyLCFoxAhUgRw)
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT

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

--Boundary_(ID_Wi9a5wxpHeyLCFoxAhUgRw)
Content-type: text/x-vcard; charset=utf-8; name=Mark_Fenbers.vcf
Content-transfer-encoding: 7BIT
Content-disposition: attachment; filename=Mark_Fenbers.vcf

begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:National Weather Service;HAS Unit - Ohio River Forecast Center
adr:;;1901 South OH-134;Wilmington;OH;45177-1908;USA
email;internet:Mark.Fenbers@noaa.gov
title:Sr. Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
note:"HAS" is an acronym for Hydrometeorological Analysis and Support
x-mozilla-html:TRUE
url:http://weather.gov/ohrfc
version:2.1
end:vcard


--Boundary_(ID_Wi9a5wxpHeyLCFoxAhUgRw)--