Thread: 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

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=f.value and f.lid=sub.lid and
sub.event_id=v.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

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