Обсуждение: Just 1 in a series...

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

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

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

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)