morethan 3 consecutive rows

[rajan1]

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

[DaPi]

Is this it?

select myTime from myTable
group  by myTime
having count(*) >= 3

[DaPi]

or maybe this is what you want:

select * from (
select myTime, subtype, value, flag,
       count(*) over (partition by myTime) cntr
from   myTable
) where cntr >= 3

[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

[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.

[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 ])

[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.

[tamilselvan]

Why don't you use simple "lead" analytical fn?

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

[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.

[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