From: msi77 <msi77@yandex.ru>
To: Mark Fenbers <mark.fenbers@noaa.gov>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregates puzzle
Date: Fri, 08 Apr 2011 14:21:02 +0400
Message-ID: <400201302258063@web25.yandex.ru> (raw)
In-Reply-To: <4D94E0CB.4020309@noaa.gov>
References: <4D94E0CB.4020309@noaa.gov>
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
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: msi77@yandex.ru, mark.fenbers@noaa.gov
Subject: Re: Aggregates puzzle
In-Reply-To: <400201302258063@web25.yandex.ru>
* 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