morethan 3 consecutive rows - Page 3

# Thread: morethan 3 consecutive rows

1. Junior Member
Join Date
Mar 2006
Posts
74
can you not just sort by ROWID to get the natural order that rows were inserted into the table? is it valid?

2. Junior Member
Join Date
Mar 2006
Posts
74
ok i finally whacked this one but for one little bug:

Code:
create table tmp_test as
select '3/1/2006 7:00a.m' as d,'a' as t,'3' as v from dual union all
select '3/1/2006 7:00a.m','a','1' from dual union all
select '3/1/2006 7:00a.m','a','1' from dual union all
select '3/1/2006 7:03a.m','a','3' from dual union all
select '3/1/2006 7:03a.m','a','4' from dual union all
select '3/1/2006 7:03a.m','a','5' from dual union all
select '3/1/2006 7:06a.m','a','6' from dual union all
select '3/1/2006 7:06a.m','a','1' from dual union all
select '3/1/2006 7:06a.m','a','2' from dual union all
select '3/1/2006 7:09a.m','a','3' from dual union all
select '3/1/2006 7:09a.m','a','5' from dual union all
select '3/1/2006 7:09a.m','a','4' from dual union all
select '3/1/2006 7:12a.m','b','3' from dual union all
select '3/1/2006 7:12a.m','b','2' from dual union all
select '3/1/2006 7:12a.m','b','1' from dual;
following my own advice (in english) given last post i made this query:

Code:
select
d,
t,
v,
case when
SUM(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 and
MIN(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 then
1
end as consec
from tmp_test
it reliably identifies consecutives and for run length of N, the last N-2 rows are flagged with a 1. i called this column CONSEC:
Code:
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	1
3/1/2006 7:06a.m	a	6	1
3/1/2006 7:06a.m	a	1
3/1/2006 7:06a.m	a	2
so now we can use a lookahead to see if the CONSEC column holds a 1 character, and because our CONSEC currently shows all but the first 2 of a consecutive range, we look ahead 2, or 1, or look at the current position:

Code:
select
d,
t,
v,
consec,
case when
consec = 1 or
lead(consec,1) OVER (ORDER BY d, rowid) = 1 or
lead(consec,2) OVER (ORDER BY d, rowid) = 1
then 'Y'
end as flag

from
(
select
d,
t,
v,
case when
SUM(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 9 and
MIN(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 then
1
end as consec
from tmp_test
);
results:

Code:
D			T	V	CONSEC	FLAG
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		Y
3/1/2006 7:03a.m	a	4		Y
3/1/2006 7:03a.m	a	5	1	Y
3/1/2006 7:06a.m	a	6	1	Y
3/1/2006 7:06a.m	a	1
3/1/2006 7:06a.m	a	2
3/1/2006 7:09a.m	a	3		Y
3/1/2006 7:09a.m	a	5		Y
3/1/2006 7:09a.m	a	4	1	Y
3/1/2006 7:12a.m	b	3	1	Y
3/1/2006 7:12a.m	b	2
3/1/2006 7:12a.m	b	1
3/1/2006 7:13a.m	c	4		Y
3/1/2006 7:14a.m	d	4		Y
3/1/2006 7:14a.m	d	5	1	Y
3/1/2006 7:14a.m	d	1
3/1/2006 7:14a.m	d	3
3/1/2006 7:14a.m	d	3
3/1/2006 7:14a.m	d	1
(i added some more values to the table after the initial create table sql)

stomped! and we have learned some interesting things about analytic functions too
Last edited by cjard; 04-12-2006 at 07:34 AM.

3. Junior Member
Join Date
Mar 2006
Posts
74
please note i forgot the question and even ignored my own advice a little, i prepared an sql for more than 2 consecutive rows, and poster wanted more than 3, so heres how we change the sql:

Code:
select
d,
t,
v,
consec,
case when
consec = 1 or
lead(consec,1) OVER (ORDER BY d, rowid) = 1 or
lead(consec,2) OVER (ORDER BY d, rowid) = 1 or
--more than 5 consec (i.e. 6+ consec) needs 5 leads
--the lookahead of this row is 3
lead(consec,3) OVER (ORDER BY d, rowid) = 1
then 'Y'
end as flag

from
(
select
d,
t,
v,
case when
--the sum over the last N rows (more than 3 rows requires 3 PRECEDING)
--must be CONSECUTIVES_REQD * MIN_THRESHOLD
--in our case we want a run of 4 or more rows with value >= 3
--so sum must be >= 4rows*3orMore == 4*3 == 12

--remember to change the X PRECEDING value of X
SUM(v) OVER (ORDER BY d, rowid ROWS 3 PRECEDING)>= 12 and
MIN(v) OVER (ORDER BY d, rowid ROWS 3 PRECEDING)>= 3 then
1
end as consec
from tmp_test
);

so for a consecutive run length N of values greater than Y you need

N-1 entries in the LEAD() section of the sql, with lead values 1 to N-1
a SUM() over the preceding N-1 rows of value >= N*Y
a MIN() over the preceding N-1 rows of value >= Y

ok, now you can edit this sql for any run length of any value

4. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally Posted by cjard
can you not just sort by ROWID to get the natural order that rows were inserted into the table? is it valid?
Of course not! ROWID has got nothing to do with what you call "natural order that rows were inserted into the table". There's no such thing in the Oracle database as built-in indicator of the inserted rows ordering. ROWID merely represents *the possition* of a particular row on the disk storage, it has no information about the time the row was inserted. And by comparing ROWIDs of two rows, you can not say which one was inserted befor the other one.

So, in short: ROWID can not be used for ordering rows in the way that original poster needs to.

5. Junior Member
Join Date
Mar 2006
Posts
74
Originally Posted by jmodic
Of course not! ROWID has got nothing to do with what you call "natural order that rows were inserted into the table". There's no such thing in the Oracle database as built-in indicator of the inserted rows ordering. ROWID merely represents *the possition* of a particular row on the disk storage, it has no information about the time the row was inserted. And by comparing ROWIDs of two rows, you can not say which one was inserted befor the other one.

So, in short: ROWID can not be used for ordering rows in the way that original poster needs to.
well, dude's going to ahve to decide on something else that is precise enough to order his rows properly then, eh? i've only ever seen rowid increment for the data work that ive been doing and its been quite a reliable thing that for two rows inserted in the same second, the rowid has been able to order them in order of creation. i'll take your advice on board though should i need it in the future. thanks!

6. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally Posted by cjard
i've only ever seen rowid increment for the data work that ive been doing and its been quite a reliable thing that for two rows inserted in the same second, the rowid has been able to order them in order of creation.
Then I suppose you've never ever observed how new rows can take place that has been releeased by deleted rows from the same table, thus obtaining a "lower" ROWIDs compared to some rows that were inserted before them? Or new table extents beeng allocated in the tablespace in such location that all rows that will end in that extent in the future will result in ROWIDs that are lower than any currently existing table's row? Or that ROWIDs of the existing rows can change for various reasons, thus making the insertion time even less corelated to the row's ROWID?

In short, as I've allready said: by comparing ROWIDs of two rows, you can definitely not conclude which of those two rows was inserted before the other one. All you can do based on their ROWIDs is *to guess*.

7. Junior Member
Join Date
Oct 2002
Posts
25
Hi all,

Finally, i got the exact requirement from the client. Please find the requirement in the attached word document.

The requirement is very scary to me. Hope the experts can help.

Thanks,
Rajan
Last edited by rajan1; 05-10-2006 at 09:42 PM.

8. The requirements STILL don't explicitly include a sort order. Your client must specify that.

Is it by (id, testdate)?
Does this produce a UNIQUE sequence?

9. Junior Member
Join Date
Oct 2002
Posts
25
Hi all,

I was bouncing back and forth with the client to confirm on the requirement. As a result i modified the requirement please find the modified requirement attached. I am trying my best to clearly define the requirement to our experts here.

1. Sort is by id and testdate
2. Select all the rows where the difference between (testdate column) current row and the lead row or the next row is 3 minutes.

Thanks,
Rajan

10. Junior Member
Join Date
Oct 2002
Posts
25
The three consecutive applies only within a date or for each date.

Thanks,
Rajan

#### Posting Permissions

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