Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    Unanswered: Need an urgent help

    Hi,

    I have a data in "myTable" as below.

    seq_no Date Flag
    1 01-Apr-2012 Y
    2 02-Apr-2012 N
    3 03-Apr-2012 N
    4 04-Apr-2012 Y
    5 05-Apr-2012 Y


    Now, I want to get 'seq_no' when last time 'flag' went to 'Y'. That means, in my case last time Flag went to 'Y' on '04-Apr-2012' and after that it is 'Y' only. So, I need output as '4'.

    Please help me urgently to develop a sql query to obtain the same.
    Please note that I have only query access to database, so I can write only SELECT queries.

    Please please please help me urgently as I have to reply on highly escalated matter.

    Thanks,
    Rohan Desai

  2. #2
    Join Date
    Apr 2012
    Posts
    5
    Even the first entry also flag contain 'Y'

    Is there any restriction that we need to consider only when flag become to 'Y'

    for second time

  3. #3
    Join Date
    Apr 2012
    Posts
    9
    As I have mentioned earlier, I want to get 'seq_no' when last time 'flag' went to 'Y'. That means, in my case last time Flag went to 'Y' on '04-Apr-2012' and after that it is 'Y' only. So, I need output as '4'.

    If there are some more records as below,

    6 06-Apr-2012 N
    7 07-Apr-2012 Y
    8 08-Apr-2012 Y


    then, last time Flag went to 'Y' on '07-Apr-2012' and after that it is 'Y' only. So, query should return output as '7'.

  4. #4
    Join Date
    Apr 2012
    Posts
    5
    Hi Rohan

    Please execute the following query and let me know tat status


    Code:
    with data as
     (select 1 no, 'Y' flag
        from dual
      union
      select 2 no, 'N' flag
        from dual
      union
      select 3 no, 'N' flag
        from dual
      union
      select 4 no, 'Y' flag
        from dual
      union
      select 5 no, 'Y' flag
        from dual
      union
      select 6 no, 'N' flag
        from dual
      union
      select 7 no, 'Y' flag
        from dual
      union
      select 8 no, 'Y' flag from dual),
    temp_1 as
     (select no, flag, dense_rank() over(partition by flag order by no) rank
        from data
       where data.flag = 'Y'),
    temp_2 as
     (select max(rank) over() max_val, no, rank from temp_1)
    select * from temp_2 where rank = max_val - 1
    Thanks
    Saipradyumn

  5. #5
    Join Date
    Apr 2012
    Posts
    9
    I have executed below query but "0 rows selected"

    Thanks,
    Rohan

  6. #6
    Join Date
    Apr 2012
    Posts
    5
    In my schema it's giving the proper results

    FYI
    Code:
    SQL> with data as
      2   (select 1 no, 'Y' flag
      3      from dual
      4    union
      5    select 2 no, 'N' flag
      6      from dual
      7    union
      8    select 3 no, 'N' flag
      9      from dual
     10    union
     11    select 4 no, 'Y' flag
     12      from dual
     13    union
     14    select 5 no, 'Y' flag
     15      from dual
     16    union
     17    select 6 no, 'N' flag
     18      from dual
     19    union
     20    select 7 no, 'Y' flag
     21      from dual
     22    union
     23    select 8 no, 'Y' flag from dual),
     24  temp_1 as
     25   (select no, flag, dense_rank() over(partition by flag order by no) rank
     26      from data
     27     where data.flag = 'Y'),
     28  temp_2 as
     29   (select max(rank) over() max_val, no, rank from temp_1)
     30  select * from temp_2 where rank = max_val - 1
     31  ;
     
       MAX_VAL         NO       RANK
    ---------- ---------- ----------
             5          7          4
     
    SQL>
    Just paste your results also
    Which version of data base you are using

    Thanks
    Saipradyumn

  7. #7
    Join Date
    Apr 2012
    Posts
    9
    Hi,

    My sql version is as below.

    C:\Documents and Settings\rd00717>sqlplus -version
    SQL*Plus: Release 10.2.0.4.0 - Production


    As metioned earlier, query doesn't produce any output.
    "0 rows selected"

    Thanks,
    Rohan

  8. #8
    Join Date
    Apr 2012
    Posts
    5
    But i am able to produce the results .
    Even if we have 5 entries only then we will get 4 as output

    FYI
    Code:
    SQL> with data as
      2   (select 1 no, 'Y' flag
      3      from dual
      4    union
      5    select 2 no, 'N' flag
      6      from dual
      7    union
      8    select 3 no, 'N' flag
      9      from dual
     10    union
     11    select 4 no, 'Y' flag
     12      from dual
     13    union
     14    select 5 no, 'Y' flag
     15      from dual
     16   /* union
     17    select 6 no, 'N' flag
     18      from dual
     19    union
     20    select 7 no, 'Y' flag
     21      from dual
     22    union
     23    select 8 no, 'Y' flag from dual
     24    */),
     25  temp_1 as
     26   (select no, flag, dense_rank() over(partition by flag order by no) rank
     27      from data
     28     where data.flag = 'Y'),
     29  temp_2 as
     30   (select max(rank) over() max_val, no, rank from temp_1)
     31  select * from temp_2 where rank = max_val - 1;
     
       MAX_VAL         NO       RANK
    ---------- ---------- ----------
             3          4          2
    Thanks
    SaiPradyumn

  9. #9
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by R0H0NS View Post
    My sql version is as below.

    C:\Documents and Settings\rd00717>sqlplus -version
    SQL*Plus: Release 10.2.0.4.0 - Production
    That's the version of SQL*Plus, not the version of your database. Run "select * from v$version" to obtain the database version.

  10. #10
    Join Date
    Jan 2012
    Posts
    84
    Maybe try in a more traditional way:
    Code:
    create table tbl(
      seq_no number primary key,
      "date" date,
      Flag varchar2(1)
    );
    
    insert into tbl
    select 1, to_date('01-Apr-2012', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American'), 'Y' 
    from dual union all
    select 2, to_date('02-Apr-2012', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American'), 'N' 
    from dual union all
    select 3, to_date('03-Apr-2012', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American'), 'N' 
    from dual union all
    select 4, to_date('04-Apr-2012', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American'), 'Y' 
    from dual union all
    select 5, to_date('05-Apr-2012', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American'), 'Y' 
    from dual 
    ;
    
    commit;
    Code:
    SELECT min( seq_no )
    FROM tbl
    WHERE seq_no > nvl( 
           (
              SELECT max( seq_no ) 
              FROM tbl
              WHERE flag = 'N'
                 AND EXISTS (
                    SELECT null 
                    FROM tbl tbl1
                    WHERE tbl1.seq_no > tbl.seq_no
                       AND tbl1.flag = 'Y'
                 )
            ), 0 )
    AND flag = 'Y';
    
    MIN(SEQ_NO)            
    ---------------------- 
    4
    This query should return a correct result even when there are records with status 'N' at the end of the table:
    Code:
    insert into tbl values ( 10, sysdate, 'N' );
    
    SELECT min( seq_no )
    FROM tbl
    WHERE seq_no > nvl( 
           (
              SELECT max( seq_no ) 
              FROM tbl
              WHERE flag = 'N'
                 AND EXISTS (
                    SELECT null 
                    FROM tbl tbl1
                    WHERE tbl1.seq_no > tbl.seq_no
                       AND tbl1.flag = 'Y'
                 )
            ), 0 )
    AND flag = 'Y';
    
    1 rows inserted.
    MIN(SEQ_NO)            
    ---------------------- 
    4

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Note: I don't know Oracle allows nesting of analytic functions.
    Example 1:
    Code:
    SELECT seq_no
         , TO_CHAR(date , 'dd-Mon-yyyy') AS date
     FROM (SELECT t.*
                , ROW_NUMBER()
                     OVER( ORDER BY
                                 CASE
                                 WHEN LAG(flag)
                                         OVER( ORDER BY date ) = 'N'
                                  AND flag = 'Y'   THEN
                                      0
                               /*ELSE 1*/
                                 END  ASC
                               , date DESC
                         ) AS r_num
            FROM  mytable t
          )
     WHERE r_num = 1
    ;

  12. #12
    Join Date
    Apr 2012
    Posts
    9
    kordirko,

    Thanks a lot for your reply. I have executed the query provided by you, but unfortunatly I am not getting any value for min(seq_no). Please see below my output.

    --------------
    MIN(SEQ_NO)|
    -------------|
    |
    --------------

    Its appearing blank. Please assist what could be the reason for this.

    Thanks,
    Rohan

  13. #13
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If sequence number always increments by one (if it is sequence driven it wont be probably) then this is a simple solution

    with x as
    (
    select 1 seq_no, '01-Apr-2012' dt, 'Y' flag from dual union all
    select 2, '02-Apr-2012', 'N' from dual union all
    select 3, '03-Apr-2012', 'N' from dual union all
    select 4, '04-Apr-2012', 'Y' from dual union all
    select 5, '05-Apr-2012', 'Y' from dual union all
    select 6, '05-Apr-2012', 'N' from dual union all
    select 7, '06-Apr-2012', 'N' from dual union all
    select 8, '07-Apr-2012', 'Y' from dual
    )
    select max(y.seq_no)
    from x, x y
    where y.seq_no = x.seq_no+1
    and x.flag='N'
    and y.flag='Y'

  14. #14
    Join Date
    Apr 2012
    Posts
    9
    Hi kordirko/AlanP,
    Let me redefine the table as below.

    PHP Code:
    KEY    SEQ_NO    DATE    FLAG
    ABC    1    1
    -Apr-2012    Y
    ABC    2    2
    -Apr-2012    N
    ABC    3    3
    -Apr-2012    N
    ABC    4    4
    -Apr-2012    Y
    ABC    5    5
    -Apr-2012    Y
    PQR    1    2
    -Apr-2012    N
    PQR    2    3
    -Apr-2012    Y
    XYZ    1    4
    -Apr-2012    Y 
    As you can see, there is one KEY column which is my primary key in table.

    When I am executing kordirko's query, I use KEY = 'ABC' to get output as 4, but as mentioned earlier I am not getting any value for min(seq_no). Its appearing blank. Please assist what could be the reason for this.


    Thanks,
    Rohan

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >As you can see, there is one KEY column which is my primary key in table.
    Primary Key does not allow duplicate values.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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