pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJust 1 in a series...
4+ messages / 3 participants
[nested] [flat]
* Just 1 in a series...
@ 2005-12-02 17:15 Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Mark Fenbers @ 2005-12-02 17:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Just 1 in a series...
@ 2005-12-02 17:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-12-02 17:20 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
Mark Fenbers <Mark.Fenbers@noaa.gov> writes:
> ... 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?
You might find the "DISTINCT ON" syntax does just what you want --- see
the "weather report" example on the SELECT reference page. It's not
standard SQL though.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Just 1 in a series...
@ 2005-12-02 17:44 Mark Fenbers <Mark.Fenbers@noaa.gov>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Mark Fenbers @ 2005-12-02 17:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>
>
>You might find the "DISTINCT ON" syntax does just what you want --- see
>the "weather report" example on the SELECT reference page. It's not
>standard SQL though.
>
This works! Thanks!
What would have to be done if I needed a standard SQL solution?
Mark
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Just 1 in a series...
@ 2005-12-05 10:43 Patrick JACQUOT <patrick.jacquot@anpe.fr>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Patrick JACQUOT @ 2005-12-05 10:43 UTC (permalink / raw)
To: pgsql-sql
Mark Fenbers wrote:
> What would have to be done if I needed a standard SQL solution?
> Mark
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Maybe you could t'ry something like :
Select whatever from yourtable a where not exists
(select * from yourtable b where b.id=a.id and b.timestamp > a.timestamp)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-12-05 10:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-02 17:15 Just 1 in a series... Mark Fenbers <Mark.Fenbers@noaa.gov>
2005-12-02 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-02 17:44 ` Mark Fenbers <Mark.Fenbers@noaa.gov>
2005-12-05 10:43 ` Patrick JACQUOT <patrick.jacquot@anpe.fr>
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