Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Aggregates puzzle
Date: Thu, 31 Mar 2011 16:14:59 -0700
Message-ID: 
 <216FFB77CBFAEE4B8EE4DF0A939FF1D14F8564@mail-001.corp.automotive.com>
In-Reply-To: <4D94E0CB.4020309@noaa.gov>
Thread-Topic: [SQL] Aggregates puzzle
Thread-Index: Acvv4GU/8z3bqkyJRnmQCfgSROryzQAGKr3Q
References: <4D94E0CB.4020309@noaa.gov>
From: "Ozer, Pam" <pozer@automotive.com>
To: "Mark Fenbers" <Mark.Fenbers@noaa.gov>,
	"PostgreSQL - SQL" <pgsql-sql@postgresql.org>

Try this.  Once you know the value you want you have to join back to
find the time of that value.

Select f.Lid, F.Value,F.event_id, f.obstime
From (
SELECT lid, MAX(value) As Value, event_id
FROM flood_ts
GROUP BY lid, event_id
)sub
Join flood f On sub.Value=3Df.value and f.lid=3Dsub.lid and
sub.event_id=3Dv.event_id


-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Mark Fenbers
Sent: Thursday, March 31, 2011 1:15 PM
To: PostgreSQL - SQL
Subject: [SQL] Aggregates puzzle

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