Skip site navigation (1) Skip section navigation (2)

Re: Aggregates puzzle

From: msi77 <msi77(at)yandex(dot)ru>
To: Mark Fenbers <mark(dot)fenbers(at)noaa(dot)gov>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Aggregates puzzle
Date: 2011-04-08 10:21:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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(dot)Fenbers(at)noaa(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:

In response to

pgsql-sql by date

Next:From: scorpdaddyDate: 2011-04-08 14:18:21
Subject: Capture insert returning
Previous:From: John FabianiDate: 2011-04-05 14:58:48
Subject: Re: is there a refactor

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group