From: msi77 <msi77@yandex.ru>
To: Mark Fenbers <mark.fenbers@noaa.gov>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4D94E0CB.4020309@noaa.gov>
References: <4D94E0CB.4020309@noaa.gov>
Subject: Re: Aggregates puzzle
MIME-Version: 1.0
Message-Id: <400201302258063@web25.yandex.ru>
Date: Fri, 08 Apr 2011 14:21:02 +0400
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=koi8-r

Try this:

select * from flood_ts F join
(SELECT lid, MAX(value) AS mvalue, event_id
FROM flood_ts
GROUP BY lid, event_id) X
on F.lid = X.lid and value = mvalue and X.event_id = F.event_id


01.04.2011, 00:15, "Mark Fenbers" <Mark.Fenbers@noaa.gov>:
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql