morethan 3 consecutive rows - Page 2

# Thread: morethan 3 consecutive rows

1. Junior Member
Join Date
Oct 2002
Posts
25
Thanks a lot. I was about to post the sorting order

Order by date,time

Sorry, i did not included Date as a column in my example above.

2. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
Is it a single date column?
Or
You store the time separately?

Tamil

3. Junior Member
Join Date
Oct 2002
Posts
25
The table has,

Date,Time,SubType,Value as columns

3/1/2006,7:00a.m,a,3
3/1/2006,7:00a.m,a,1
3/1/2006,7:00a.m,a,1
3/1/2006,7:03a.m,a,3
3/1/2006,7:03a.m,a,4
3/1/2006,7:03a.m,a,5
3/1/2006,7:06a.m,a,6
3/1/2006,7:06a.m,a,1
3/1/2006,7:06a.m,a,2
3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,4
3/1/2006,7:12a.m,b,3

3/1/2006,7:12a.m,b,2
3/1/2006,7:12a.m,b,1

4. Sorting by date/time (why don't you use an Oracle DATE, which includes both?) will not be sufficient to get deterministic results. As you can see by the following possible retrieval orders:
Code:
```3/1/2006,7:09a.m,a,1
3/1/2006,7:09a.m,a,2
3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,4
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,6
3/1/2006,7:09a.m,a,7
3/1/2006,7:09a.m,a,8
3/1/2006,7:09a.m,a,9

and

3/1/2006,7:09a.m,a,3
3/1/2006,7:09a.m,a,4
3/1/2006,7:09a.m,a,1
3/1/2006,7:09a.m,a,5
3/1/2006,7:09a.m,a,6
3/1/2006,7:09a.m,a,2
3/1/2006,7:09a.m,a,7
3/1/2006,7:09a.m,a,8
3/1/2006,7:09a.m,a,9```

5. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Here is one of the possible solutions.

(Note that for the simplicity of this case I deliberately made the data in the TIME column unique, so that we don't have a unique sorting problem that DaPi is refering to in the previous post.)

Code:
```SQL> select * from tab1 order by time;

TIME     SUBTYPE      VALUE
-------- ------- ----------
07:00:00 a                2
07:00:20 a                0
07:00:40 a                5
07:03:00 a                3
07:03:20 a                2
07:03:40 a                1
07:06:00 a                3
07:06:20 a                3
07:06:40 a                4
07:09:00 b                5
07:09:20 b                2
07:09:40 b                1

12 rows selected.

SQL> select
2    time, subtype, value,
3    case when preceding = 3 or following = 3 then 'y' end as flag
4  from (select
5          time, subtype, value, indicator3, rownum,
6          count(indicator3) over(order by rownum rows between 2 preceding and current row) as preceding,
7          count(indicator3) over(order by rownum rows between current row and 2 following) as following
8        from (select
9                time, subtype, value,
10                case when value >= 3 then 1 end as indicator3
11              from tab1
12              order by time
13             )
14       );

TIME     SUBTYPE      VALUE FLAG
-------- ------- ---------- ----
07:00:00 a                2
07:00:20 a                0
07:00:40 a                5
07:03:00 a                3
07:03:20 a                2
07:03:40 a                1
07:06:00 a                3 y
07:06:20 a                3 y
07:06:40 a                4 y
07:09:00 b                5 y
07:09:20 b                2
07:09:40 b                1

12 rows selected.

SQL>```

6. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
Thanks Modic.

Even the time column is not unique, I can generate artifical unique key using rownum.

Below is the SQL that did not use analytical function.

Code:
```SQL> select  * from test ;

RTIME      T      VALUE
---------- - ----------
7.00       a          2
7.00       a          0
7.00       a          5
7.03       a          2
7.06       a          3
7.06       a          3
7.06       a          4
7.09       b          5
7.09       b          2
7.09       b          1

10 rows selected.

select distinct *
from (
select *
from (  select rn, rtime,  least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from (  select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn +1 = iv1.rn
)
and  iv1.leastval = ( select leastval
from (  select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn +2 = iv1.rn
)
union all
select *
from (  select rn, rtime,  least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from (  select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn -1 = iv1.rn
)
and  iv1.leastval = ( select leastval
from (  select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn +1 = iv1.rn
)
union all
select *
from (  select rn, rtime,  least(3,value) leastval
from (select rownum rn, a.*
from test a order by rtime) ) iv1
where leastval = 3
and iv1.leastval = ( select leastval
from (  select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv2
where iv2.rn -1 = iv1.rn
)
and  iv1.leastval = ( select leastval
from (  select rn,
least(3,value) leastval
from (select rownum rn, a.*
from test a
order by rtime) ) iv3
where iv3.rn -2 = iv1.rn
)
)
/

RN RTIME        LEASTVAL
---------- ---------- ----------
5 7.06                3
6 7.06                3
7 7.06                3
8 7.09                3```
I will rewrite it using analytical function.

Tamil

7. Originally Posted by tamilselvan
Even the time column is not unique, I can generate artifical unique key using rownum.
Hi Tamil,

That's still not going to resolve the ordering problem in general - rownum is defined at the level of the result of a select, which may be effectively random inside one value of date/time. I suspect the OP will have to introduce a sequence number at the point at which the rows are inserted in the table, to have a bomb-proof solution.

If an update is required (as I suspect) then it could be done via ROWID:
Update myTable Set flag='Y'
Where rowid in
(a version of Juri's select statement, including rowid)

8. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
That's still not going to resolve the ordering problem in general - rownum is defined at the level of the result of a select, which may be effectively random inside one value of date/time.
We can assign the rownum only on the sorted result set. In this case we can get what we want.

Using rowid for update. You are right. If we want to update another column in the table, then we must use rowid for update.

Tamil

9. Junior Member
Join Date
Oct 2002
Posts
25
Thanks everybody for the response. Can I suggest the DBA team to make the two columns Date and Time into one column as Date? In this way we can sort by Date.

10. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
Originally Posted by rajan1
Thanks everybody for the response. Can I suggest the DBA team to make the two columns Date and Time into one column as Date? In this way we can sort by Date.
You are right. Then add date col in the order by clause.

OR

Even after you combine those 2 columns, they will not form a unique key. I suggest you should add a running seq col into the table, and make it as PK.

Tamil
Last edited by tamilselvan; 04-06-2006 at 09:59 AM.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•