# Thread: morethan 3 consecutive rows

1. Junior Member
Join Date
Oct 2002
Posts
25

## morethan 3 consecutive rows

We have to find and flag the occurance of values >=3 if it happens more than 3 consecutive cycles/rows.It could happen within same subtype or different subtypes

example:-

time subtype value flag
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3 Y
7:06 a 3 Y
7:06 a 4 Y
7:09 b 5 Y
7:09 b 2
7:09 b 1
Last edited by rajan1; 03-31-2006 at 02:38 PM.

2. Is this it?
Code:
```select myTime from myTable
group  by myTime
having count(*) >= 3```

3. or maybe this is what you want:
Code:
```select * from (
select myTime, subtype, value, flag,
count(*) over (partition by myTime) cntr
from   myTable
) where cntr >= 3```

4. Junior Member
Join Date
Mar 2006
Posts
74
sounds more complicated.. i think it will be a sliding window, ordered by time, looking back over 4 rows, to see if the sum value over the 4 rows is >= 12 and the min value for the 4 rows is >= 3

thing is.. dude hasnt said which rows he wants to flag

5. Junior Member
Join Date
Oct 2002
Posts
25
Thanks a lot for the response.

Sorry,my bad. This is how the data looks like in the table case_study

time subtype value
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3
7:06 a 3
7:06 a 4
7:09 b 5

7:09 b 2
7:09 b 1

In this we need to find value >=3 when it occurs three or more than three consecutive times/rows. In the above example the bold rows have values in the value column >=3 and it occurs more than 3 times consecutively. We have to flag or alert such rows.

6. Am I right in thinking that the table doesn't have a primary key?

If I'm right, this is going to be painful.

P.S. I might have spotted that "value" was a column name if you'd formated your post with code tags (the # icon produces [ code ] and [ / code ])

7. Junior Member
Join Date
Oct 2002
Posts
25
Yes, there is no primary key.

Here is one SQL (in bold) i found while googling, here the author compare the values between consecutive rows and gets the difference between two consecutive row values.I am not able to apply this to my requirement.

I want to compare consecutive rows in a table, e.g., 1-2, 2-3, etc. Now I was thinking of doing a self join for this. But the problem is, how do I do this, as none of the columns are predictable. For example, if I have the following data:

name quantity cost
A 2 20
H 7 24
L 1 12
...
and I want to find out the difference between the cost of A and H, H and L, and so on.

This question posed on 06 January 2006

Interesting question. We need to join each row to the row which has the lowest name of all the names that are greater than the name in the row being joined. Simple, yes? I mean, once you say it like that, it's easy to understand, right?

Well, once you say it like that, it's easy to write the SQL for, too.

select t1.name
, t1.cost as t1_cost
, t2.cost as t2_cost
, t2.cost - t1.cost as diff
from yourtable as t1
left outer
join yourtable as t2
on t2.name
= ( select min(name)
from yourtable
where name > t1.name )

name t1_cost t2_cost diff
A 20 24 4
H 24 12 -12
L 12 -- --
It's an unusual ON condition, because you don't see one like this every day. It may be rare but it is perfectly valid.

Thanks for the tips.

8. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
Why don't you use simple "lead" analytical fn?

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

NAME              QTY       COST
---------- ---------- ----------
A                   2         20
H                   7         24
L                   1         12

SQL> get int2
1  select name, qty, cost,
2         lead(cost) over ( order by name) nextcost
3* from  int
SQL> /

NAME              QTY       COST   NEXTCOST
---------- ---------- ---------- ----------
A                   2         20         24
H                   7         24         12
L                   1         12```
Tamil

9. Junior Member
Join Date
Oct 2002
Posts
25
Thanks a lot for your response.

I really don't want the difference between consecutive rows. What i really want is the occurance of more than 3 consecutive rows having value>=3

time subtype value
7:00 a 2
7:00 a 0
7:00 a 5
7:03 a 3
7:03 a 2
7:03 a 1
7:06 a 3
7:06 a 3
7:06 a 4
7:09 b 5

7:09 b 2
7:09 b 1

In the above example the bold rows have values in the value column >=3 and it occurs more than 3 times consecutively. We have to flag or alert such rows.

10. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
You said the table does not have PK.

Then what is the sorting order to determine 3 consecutive rows that have value >= 3?

Tamil

#### Posting Permissions

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

Click Here to Expand Forum to Full Width

<
×
By using this site, you agree to the Privacy Policy