# morethan 3 consecutive rows

Show 40 post(s) from this thread on one page
Page 1 of 4 123 ... Last
• 03-31-2006, 02:35 PM
rajan1
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
• 04-01-2006, 12:32 AM
DaPi
Is this it?
Code:

```select myTime from myTable group  by myTime having count(*) >= 3```
• 04-01-2006, 01:17 PM
DaPi
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```
• 04-03-2006, 05:48 AM
cjard
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
• 04-03-2006, 04:11 PM
rajan1
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.
• 04-03-2006, 05:08 PM
DaPi
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 ])
• 04-03-2006, 08:17 PM
rajan1
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.
• 04-04-2006, 02:55 PM
tamilselvan
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
• 04-04-2006, 03:16 PM
rajan1
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.
• 04-04-2006, 03:29 PM
tamilselvan
You said the table does not have PK.

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

Tamil
Show 40 post(s) from this thread on one page
Page 1 of 4 123 ... Last