From: Mark Fenbers <Mark.Fenbers@noaa.gov>
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Subject: Aggregates puzzle
Date: Thu, 31 Mar 2011 16:15:07 -0400
Message-ID: <4D94E0CB.4020309@noaa.gov> (raw)
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
Attachments:
[text/x-vcard] Mark_Fenbers.vcf (407B, ../4D94E0CB.4020309@noaa.gov/2-Mark_Fenbers.vcf)
download | inline:
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Mark.Fenbers@noaa.gov
Subject: Re: Aggregates puzzle
In-Reply-To: <4D94E0CB.4020309@noaa.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox