pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
select by streak
3+ messages / 2 participants
[nested] [flat]

* select by streak
@ 2001-06-20 19:35  Joseph Shraibman <jks@selectacast.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Joseph Shraibman @ 2001-06-20 19:35 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: select by streak
@ 2001-06-21 09:29  Richard Huxton <dev@archonet.com>
  parent: Joseph Shraibman <jks@selectacast.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Richard Huxton @ 2001-06-21 09:29 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: select by streak
@ 2001-06-21 19:04  Joseph Shraibman <jks@selectacast.net>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Joseph Shraibman @ 2001-06-21 19:04 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-06-21 19:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-20 19:35 select by streak Joseph Shraibman <jks@selectacast.net>
2001-06-21 09:29 ` Richard Huxton <dev@archonet.com>
2001-06-21 19:04   ` Joseph Shraibman <jks@selectacast.net>

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