Обсуждение: select by streak

Lets say I have a table like this:

id int  <== primary key
ts timestamp 
name text
status char

status is G for good or B for bad.  How do I do something like:
SELECT name FROM mytable WHERE <last 3 entires in a row have status =
'B'> and <has been no good ones in the past week> ?

-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

From: "Joseph Shraibman" <jks@selectacast.net>

> Lets say I have a table like this:
>
> id int  <== primary key
> ts timestamp
> name text
> status char
>
> status is G for good or B for bad.  How do I do something like:
> SELECT name FROM mytable WHERE <last 3 entires in a row have status =
> 'B'> and <has been no good ones in the past week> ?

You're looking at a procedural solution here rather than SQL I'd suspect.
Either do this in your application or look into plpgsql or pltcl.

You might be able to do something with LIMIT and subselects (which I believe
cooperate in 7.1 - but test it) but it isn't going to be pretty.

HTH

- Richard Huxton

Richard Huxton wrote:
> 
> From: "Joseph Shraibman" <jks@selectacast.net>
> 
> > Lets say I have a table like this:
> >
> > id int  <== primary key
> > ts timestamp
> > name text
> > status char
> >
> > status is G for good or B for bad.  How do I do something like:
> > SELECT name FROM mytable WHERE <last 3 entires in a row have status =
> > 'B'> and <has been no good ones in the past week> ?
> 
> You're looking at a procedural solution here rather than SQL I'd suspect.
> Either do this in your application or look into plpgsql or pltcl.

That's what I was afraid of :(


-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com