pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregates puzzle
3+ messages / 3 participants
[nested] [flat]
* Aggregates puzzle
@ 2011-03-31 20:15 Mark Fenbers <Mark.Fenbers@noaa.gov>
2011-03-31 23:14 ` Re: Aggregates puzzle Ozer, Pam <pozer@automotive.com>
2011-04-08 10:21 ` Re: Aggregates puzzle msi77 <msi77@yandex.ru>
0 siblings, 2 replies; 3+ messages in thread
From: Mark Fenbers @ 2011-03-31 20:15 UTC (permalink / raw)
To: pgsql-sql
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
Attachments:
[text/x-vcard] Mark_Fenbers.vcf (407B, ../../4D94E0CB.4020309@noaa.gov/2-Mark_Fenbers.vcf)
download | inline:
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:National Weather Service;HAS Unit - Ohio River Forecast Center
adr:;;1901 South OH-134;Wilmington;OH;45177-1908;USA
email;internet:Mark.Fenbers@noaa.gov
title:Sr. Meteorologist
tel;work:937-383-0430
tel;fax:937-383-0033
note:"HAS" is an acronym for Hydrometeorological Analysis and Support
x-mozilla-html:TRUE
url:http://weather.gov/ohrfc
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggregates puzzle
2011-03-31 20:15 Aggregates puzzle Mark Fenbers <Mark.Fenbers@noaa.gov>
@ 2011-03-31 23:14 ` Ozer, Pam <pozer@automotive.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ozer, Pam @ 2011-03-31 23:14 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Aggregates puzzle
2011-03-31 20:15 Aggregates puzzle Mark Fenbers <Mark.Fenbers@noaa.gov>
@ 2011-04-08 10:21 ` msi77 <msi77@yandex.ru>
1 sibling, 0 replies; 3+ messages in thread
From: msi77 @ 2011-04-08 10:21 UTC (permalink / raw)
To: Mark Fenbers <mark.fenbers@noaa.gov>; +Cc: 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.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-04-08 10:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-31 20:15 Aggregates puzzle Mark Fenbers <Mark.Fenbers@noaa.gov>
2011-03-31 23:14 ` Ozer, Pam <pozer@automotive.com>
2011-04-08 10:21 ` msi77 <msi77@yandex.ru>
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