Re: Help: a difficult SQL request

Sorry, it does not work, it returns too many rows, but I believe it is only minor problem, which can be fixed easily by studying how to use connect by.

Thanks for so many responses.

"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0302250859.6ca083ef_at_posting.google.com...
> "charlie cs" <cs3526(no-spam)@yahoo.com> wrote in message
news:<jvP5a.9872$_J5.3067_at_nwrddc01.gnilink.net>...
> > we have a huge table, with data coming in every minute, table will be
like
> > this
> > TIME VOLULME
> > --------------------
> > 1:01 12
> > 1:02 13
> > 1:04 15
> > 1:05 3
> > .......
> >
> > but sometimes the data collector went bad, so the volume will inherit
the
> > privious one, the data will be like
> >
> > TIME VOLULME
> > --------------------
> > 1:01 12
> > 1:02 13
> > 1:04 15
> > 1:05 3
> > .......
> > 1.10 3
> > 1.11 3
> > 1.12 3
> > 1.13 3
> > 1.14 3
> > 1.15 3
> > 1.16 3
> > ...............
> >
> > How could we find those records which did change for at least 8 minutes?
> >
> > Thanks for your help
>
> Use connect by:
>
> select t.time, t.volume
> from your_table t,
> (select level ll, time, volume
> from your_table
> connect by time = prior time + .01
> and volume = prior volume) c
> where c.ll >= 8
> and t.time between c.time - c.ll * .01 and c.time
>
> If time column is indexed, it should run pretty fast. You can restrict
time
> range using START WITH clause.
Received on Tue Feb 25 2003 - 17:15:05 CST