morethan 3 consecutive rows - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31

Thread: morethan 3 consecutive rows

  1. #11
    Join Date
    Oct 2002
    Posts
    25
    Thanks a lot. I was about to post the sorting order

    Order by date,time

    Sorry, i did not included Date as a column in my example above.

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Is it a single date column?
    Or
    You store the time separately?

    Tamil

  3. #13
    Join Date
    Oct 2002
    Posts
    25
    The table has,

    Date,Time,SubType,Value as columns

    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
    3/1/2006,7:03a.m,a,4
    3/1/2006,7:03a.m,a,5
    3/1/2006,7:06a.m,a,6
    3/1/2006,7:06a.m,a,1
    3/1/2006,7:06a.m,a,2
    3/1/2006,7:09a.m,a,3
    3/1/2006,7:09a.m,a,5
    3/1/2006,7:09a.m,a,4
    3/1/2006,7:12a.m,b,3

    3/1/2006,7:12a.m,b,2
    3/1/2006,7:12a.m,b,1

  4. #14
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Sorting by date/time (why don't you use an Oracle DATE, which includes both?) will not be sufficient to get deterministic results. As you can see by the following possible retrieval orders:
    Code:
    3/1/2006,7:09a.m,a,1
    3/1/2006,7:09a.m,a,2
    3/1/2006,7:09a.m,a,3
    3/1/2006,7:09a.m,a,4
    3/1/2006,7:09a.m,a,5
    3/1/2006,7:09a.m,a,6 
    3/1/2006,7:09a.m,a,7
    3/1/2006,7:09a.m,a,8
    3/1/2006,7:09a.m,a,9
    
    and
    
    3/1/2006,7:09a.m,a,3
    3/1/2006,7:09a.m,a,4
    3/1/2006,7:09a.m,a,1
    3/1/2006,7:09a.m,a,5
    3/1/2006,7:09a.m,a,6
    3/1/2006,7:09a.m,a,2 
    3/1/2006,7:09a.m,a,7
    3/1/2006,7:09a.m,a,8
    3/1/2006,7:09a.m,a,9
    "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

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Here is one of the possible solutions.

    (Note that for the simplicity of this case I deliberately made the data in the TIME column unique, so that we don't have a unique sorting problem that DaPi is refering to in the previous post.)

    Code:
    SQL> select * from tab1 order by time;
    
    TIME     SUBTYPE      VALUE
    -------- ------- ----------
    07:00:00 a                2
    07:00:20 a                0
    07:00:40 a                5
    07:03:00 a                3
    07:03:20 a                2
    07:03:40 a                1
    07:06:00 a                3
    07:06:20 a                3
    07:06:40 a                4
    07:09:00 b                5
    07:09:20 b                2
    07:09:40 b                1
    
    12 rows selected.
    
    SQL> select
      2    time, subtype, value,
      3    case when preceding = 3 or following = 3 then 'y' end as flag
      4  from (select 
      5          time, subtype, value, indicator3, rownum,
      6          count(indicator3) over(order by rownum rows between 2 preceding and current row) as preceding,
      7          count(indicator3) over(order by rownum rows between current row and 2 following) as following
      8        from (select
      9                time, subtype, value,
     10                case when value >= 3 then 1 end as indicator3
     11              from tab1
     12              order by time
     13             )
     14       );
    
    TIME     SUBTYPE      VALUE FLAG
    -------- ------- ---------- ----
    07:00:00 a                2
    07:00:20 a                0
    07:00:40 a                5
    07:03:00 a                3
    07:03:20 a                2
    07:03:40 a                1
    07:06:00 a                3 y
    07:06:20 a                3 y
    07:06:40 a                4 y
    07:09:00 b                5 y
    07:09:20 b                2
    07:09:40 b                1
    
    12 rows selected.
    
    SQL>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #16
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Thanks Modic.

    Even the time column is not unique, I can generate artifical unique key using rownum.

    Below is the SQL that did not use analytical function.

    Code:
    SQL> select  * from test ;
    
    RTIME      T      VALUE
    ---------- - ----------
    7.00       a          2
    7.00       a          0
    7.00       a          5
    7.03       a          2
    7.06       a          3
    7.06       a          3
    7.06       a          4
    7.09       b          5
    7.09       b          2
    7.09       b          1
    
    10 rows selected.
    
    select distinct *
    from (
    select *
      from (  select rn, rtime,  least(3,value) leastval
                from (select rownum rn, a.*
                        from test a order by rtime) ) iv1
    where leastval = 3
      and iv1.leastval = ( select leastval
                             from (  select rn, 
                                                least(3,value) leastval
                                       from (select rownum rn, a.*
                                               from test a
                                              order by rtime) ) iv2
                             where iv2.rn +1 = iv1.rn
                          )
     and  iv1.leastval = ( select leastval
                             from (  select rn, 
                                                least(3,value) leastval
                                       from (select rownum rn, a.*
                                                from test a
                                               order by rtime) ) iv3
                            where iv3.rn +2 = iv1.rn
                          )
    union all
    select *
      from (  select rn, rtime,  least(3,value) leastval
                from (select rownum rn, a.*
                        from test a order by rtime) ) iv1
    where leastval = 3
      and iv1.leastval = ( select leastval
                             from (  select rn, 
                                                least(3,value) leastval
                                       from (select rownum rn, a.*
                                               from test a
                                              order by rtime) ) iv2
                             where iv2.rn -1 = iv1.rn
                          )
     and  iv1.leastval = ( select leastval
                             from (  select rn, 
                                                least(3,value) leastval
                                       from (select rownum rn, a.*
                                                from test a
                                               order by rtime) ) iv3
                            where iv3.rn +1 = iv1.rn
                          )
    union all
    select *
      from (  select rn, rtime,  least(3,value) leastval
                from (select rownum rn, a.*
                        from test a order by rtime) ) iv1
    where leastval = 3
      and iv1.leastval = ( select leastval
                             from (  select rn, 
                                                least(3,value) leastval
                                       from (select rownum rn, a.*
                                               from test a
                                              order by rtime) ) iv2
                             where iv2.rn -1 = iv1.rn
                          )
     and  iv1.leastval = ( select leastval
                             from (  select rn, 
                                                least(3,value) leastval
                                       from (select rownum rn, a.*
                                                from test a
                                               order by rtime) ) iv3
                            where iv3.rn -2 = iv1.rn
                          )
    )
    /
    
            RN RTIME        LEASTVAL
    ---------- ---------- ----------
             5 7.06                3
             6 7.06                3
             7 7.06                3
             8 7.09                3
    I will rewrite it using analytical function.

    Tamil

  7. #17
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Quote Originally Posted by tamilselvan
    Even the time column is not unique, I can generate artifical unique key using rownum.
    Hi Tamil,

    That's still not going to resolve the ordering problem in general - rownum is defined at the level of the result of a select, which may be effectively random inside one value of date/time. I suspect the OP will have to introduce a sequence number at the point at which the rows are inserted in the table, to have a bomb-proof solution.

    If an update is required (as I suspect) then it could be done via ROWID:
    Update myTable Set flag='Y'
    Where rowid in
    (a version of Juri's select statement, including rowid)
    "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

  8. #18
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    That's still not going to resolve the ordering problem in general - rownum is defined at the level of the result of a select, which may be effectively random inside one value of date/time.
    I thought about that. That's why I asked the original poster about the sorting order.
    We can assign the rownum only on the sorted result set. In this case we can get what we want.

    Using rowid for update. You are right. If we want to update another column in the table, then we must use rowid for update.

    Tamil

  9. #19
    Join Date
    Oct 2002
    Posts
    25
    Thanks everybody for the response. Can I suggest the DBA team to make the two columns Date and Time into one column as Date? In this way we can sort by Date.

  10. #20
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by rajan1
    Thanks everybody for the response. Can I suggest the DBA team to make the two columns Date and Time into one column as Date? In this way we can sort by Date.
    You are right. Then add date col in the order by clause.

    OR

    Even after you combine those 2 columns, they will not form a unique key. I suggest you should add a running seq col into the table, and make it as PK.

    Tamil
    Last edited by tamilselvan; 04-06-2006 at 10:59 AM.

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