DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: morethan 3 consecutive rows

  1. #1
    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 03:38 PM.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Is this it?
    Code:
    select myTime from myTable
    group  by myTime
    having count(*) >= 3
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    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. #5
    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. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 ])
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  7. #7
    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. #8
    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. #9
    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. #10
    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