From: Mark Fenbers <Mark.Fenbers@noaa.gov>
To: PostgreSQL - SQL <pgsql-sql@postgresql.org>
Subject: Just 1 in a series...
Date: Fri, 02 Dec 2005 12:15:29 -0500
Message-ID: <43908131.9060403@noaa.gov> (raw)
I currently have a working SQL that SELECTs all records whose
'river_stage' column exceeds the 'flood_stage' column. (Very simple --
no applause needed.) Typically, if I get one record, I get a
consecutive series of them since rivers rise and fall in a continuous
fashion, and usually respond lethargically when this much water is in
the rivers. This time-series of river stages all have (another column
called) 'event_id' set to the same integer value, so long as the river
has not fallen below flood stage (which will trigger the event_ID to be
incremented).
However, I only want the first occurrence of a such a series (where the
event_id is the same), what SQL syntax should I use to do this?
I tried playing with different combinations using DISTINCT, GROUP BY,
and LIMIT 1, but I have had no success getting the results I'm looking
for, thus far. So I figured I might get farther faster by asking the
group. I must be misunderstanding the "GROUP BY" clause because I get
an error essentially stating that I need to list every column in the
SELECT list in the GROUP BY list (which makes it ineffective)...
My knots are tangled. Can someone please send advice regarding this issue?
Mark
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: Mark.Fenbers@noaa.gov
Subject: Re: Just 1 in a series...
In-Reply-To: <43908131.9060403@noaa.gov>
* 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