7 Replies Latest reply: Feb 12, 2013 8:50 PM by spur230 RSS

    SqL query help

    spur230
      I am using Oracle 11.2.03 database.
      with tmp  as (
      select  'A'  Code,  1 seq ,  'FAIL' status, 1 ID from dual union all
      select  'A'  Code,  2 seq ,  'FAIL' status, 2 ID from dual union all
      select  'A'  Code,  3 seq ,  'PASS' status, 3 ID from dual union all
      select  'A'  Code,  4 seq ,  'PASS' status, 4 ID from dual union all
      select  'A'  Code,  5 seq ,  'PASS' status, 5 ID from dual union all
      select  'B'  Code,  1 seq ,  'FAIL' status, 6 ID from dual union all
      select  'B'  Code,  2 seq ,  'FAIL' status, 7 ID from dual union all
      select  'C'  Code,  1 seq ,  'FAIL' status, 8 ID from dual union all
      select  'C'  Code,  2 seq ,  'PASS' status, 9 ID from dual 
      )select * from tmp
      Code    Seq   Status    ID
      A     1     FAIL     1
      A     2     FAIL     2
      A     3     PASS     3
      A     4     PASS     4
      A     5     PASS     5
      B     1     FAIL     6
      B     2     FAIL     7
      C     1     FAIL     8
      C     2     PASS     9
      Requirement
      Code should be map to ID when it is first PASS based on seq , if it NEVER passed than map CD to latest ID (max seq for the code )

      I need a query to get following output

      Output
      ---------
      Code   ID 
      A      3
      B      7
      C      9
      (A first changed to PASS for ID 3,B does not have PASS so taking latest record which is 7, C changed to PASS for ID 9)

      Many thanks for your input.
        • 1. Re: SqL query help
          ranit B
          something like this ??
          select 
            code, 
            decode(mx,'PASS',MIN(id),MAX(id))
          from (
            select 
              code, status, id,
              max(status) over(partition by code) mx
            from tem -- "your table"
          )
          where
            status = mx
          group by code,mx
          order by code;
          Output :
          A     3
          B     7
          C     9
          HTH
          Ranit B.
          • 2. Top-N Query
            Frank Kulash
            Hi,

            Here's One way:
            WITH     got_r_num     AS
            (
                 SELECT     code, id
                 ,     ROW_NUMBER () OVER ( PARTITION BY  code
                                           ORDER BY          status     DESC
                                     ,                CASE
                                                      WHEN  status = 'PASS'
                                                 THEN  seq
                                                 ELSE  -seq
                                                  END
                                   )   AS r_num
                 FROM    tmp
                 WHERE     status     IN ('PASS', 'FAIL')
            )
            SELECT       code, id
            FROM       got_r_num
            WHERE       r_num     = 1
            ORDER BY  code
            ;
            This is an example of a Top-N Query , where you want to pick N items (N=1 in this case) from the top of an ordered list. The problem here is how to order the rows. Rows chosen will have either status 'PASS' or 'FAIL', and you want to pick a 'PASS' row whenever there is one, so we need to order by status (in DESCending order) first. Now comes the tricky part: if there are multiple rows with the same status, then we need to sort them by seq, but for 'PASS' rows we want the lowest seq, while for 'FAIL' rows we want the highest seq. The query above picks the lowest seq, but first it changes the sign of seq for 'FAIL' rows, so that (for example) -2 comes before -1. This assumes that seq is never negative.
            • 3. Re: SqL query help
              ranit B
              Spur,
              /*just checked your concern, so removing these text*/

              Can you please give some test data covering all your scenarios?

              Btw, my name's Ranit ;)

              Edited by: ranit B on Feb 13, 2013 1:33 AM
              • 4. Re: SqL query help
                spur230
                Thanks Ranit.
                It makes sense. but can we do it without using max(status) because in my actual table I have many statuses like Withdawn, cancel .So using max(status) will not give correct output.

                In actual scenario I have following scenario
                with tmp  as (
                select  'A'  Code,  1 seq ,  'LATE_CANCEL' status, 1 ID from dual union all
                select  'A'  Code,  2 seq ,  'FAIL' status, 2 ID from dual union all
                select  'A'  Code,  3 seq ,  'PASS' status, 3 ID from dual union all
                select  'A'  Code,  4 seq ,  'PASS' status, 4 ID from dual union all
                select  'A'  Code,  5 seq ,  'EXPIRED' status, 5 ID from dual union all
                select  'B'  Code,  1 seq ,  'LATE_CANCEL' status, 6 ID from dual union all
                select  'B'  Code,  2 seq ,  'FAIL' status, 7 ID from dual union all
                select  'C'  Code,  1 seq ,  'EXPIRED' status, 8 ID from dual union all
                select  'C'  Code,  2 seq ,  'PASS' status, 9 ID from dual  union all
                select  'D'  Code,  1 seq ,  'FAIL' status, 10 ID from dual union all
                select  'D'  Code,  2 seq ,  'LATE_CANCEL' status, 11 ID from dual  union all
                select  'D'  Code,  3 seq ,  'EXPIRED' status, 12  from dual  union all
                select  'D'  Code,  4 seq ,  'EXPIRED' status, 13  from dual  union all
                select  'D'  Code,  5 seq ,  'PASS' status, 14  from dual  
                ) select  * from tmp
                MAP CODE to ID
                case 1 (code has both 'LATE_CANCEL' OR FAIL and Other status )
                - Get latest record which is NOT 'LATE_CANCEL' OR FAIL
                case 2 (code has only LATE_CANCEL or FAIL status )
                -Get the LATEST record
                case 3 (Code does not have any LATE_CANCEL or FAIL status)
                Code and ID will remain same

                The output should be
                code ID
                A    3
                B    7
                C    8
                C    9
                D    12
                Here, For A ID is 3 because its the first record which is not 'LATE_CANCEL' OR FAIL
                for B ID IS 7 because there is no other record beside 'LATE_CANCEL' OR FAIL so we get latest record
                for C it is both 8 and 9 because it does not have any 'LATE_CANCEL' OR FAIL records so it will remain as it is
                for D it is 12 because it is first record which is not 'LATE_CANCEL' OR FAIL
                • 5. Re: SqL query help
                  ---Brodyaga---
                  Hi

                  Maybe this query help?
                  with tmp  as (select  'A'  Code,  1 seq ,  'LATE_CANCEL' status, 1 ID from dual union all
                                select  'A'  Code,  2 seq ,  'FAIL' status, 2 ID from dual union all
                                select  'A'  Code,  3 seq ,  'PASS' status, 3 ID from dual union all
                                select  'A'  Code,  4 seq ,  'PASS' status, 4 ID from dual union all
                                select  'A'  Code,  5 seq ,  'EXPIRED' status, 5 ID from dual union all
                                select  'B'  Code,  1 seq ,  'LATE_CANCEL' status, 6 ID from dual union all
                                select  'B'  Code,  2 seq ,  'FAIL' status, 7 ID from dual union all
                                select  'C'  Code,  1 seq ,  'EXPIRED' status, 8 ID from dual union all
                                select  'C'  Code,  2 seq ,  'PASS' status, 9 ID from dual  union all
                                select  'D'  Code,  1 seq ,  'FAIL' status, 10 ID from dual union all
                                select  'D'  Code,  2 seq ,  'LATE_CANCEL' status, 11 ID from dual  union all
                                select  'D'  Code,  3 seq ,  'EXPIRED' status, 12  from dual  union all
                                select  'D'  Code,  4 seq ,  'EXPIRED' status, 13  from dual  union all
                                select  'D'  Code,  5 seq ,  'PASS' status, 14  from dual) 
                  select code,
                         nvl2(max(decode(status, 'PASS', status)),
                              min(id) keep (dense_rank first order by decode(status, 'PASS', status)),
                              max(id))
                  from tmp
                  group by code
                  CODE ID                      
                  ---- ----------------------- 
                  A    3
                  B    7
                  C    9
                  D    14
                  • 6. Re: SqL query help
                    Frank Kulash
                    Hi,
                    spur230 wrote:
                    Thanks Ranit.
                    It makes sense. but can we do it without using max(status) because in my actual table I have many statuses like Withdawn, cancel .So using max(status) will not give correct output.

                    In actual scenario I have following scenario
                    with tmp  as (
                    select  'A'  Code,  1 seq ,  'LATE_CANCEL' status, 1 ID from dual union all
                    select  'A'  Code,  2 seq ,  'FAIL' status, 2 ID from dual union all
                    select  'A'  Code,  3 seq ,  'PASS' status, 3 ID from dual union all
                    select  'A'  Code,  4 seq ,  'PASS' status, 4 ID from dual union all
                    select  'A'  Code,  5 seq ,  'EXPIRED' status, 5 ID from dual union all
                    select  'B'  Code,  1 seq ,  'LATE_CANCEL' status, 6 ID from dual union all
                    select  'B'  Code,  2 seq ,  'FAIL' status, 7 ID from dual union all
                    select  'C'  Code,  1 seq ,  'EXPIRED' status, 8 ID from dual union all
                    select  'C'  Code,  2 seq ,  'PASS' status, 9 ID from dual  union all
                    select  'D'  Code,  1 seq ,  'FAIL' status, 10 ID from dual union all
                    select  'D'  Code,  2 seq ,  'LATE_CANCEL' status, 11 ID from dual  union all
                    select  'D'  Code,  3 seq ,  'EXPIRED' status, 12  from dual  union all
                    select  'D'  Code,  4 seq ,  'EXPIRED' status, 13  from dual  union all
                    select  'D'  Code,  5 seq ,  'PASS' status, 14  from dual  
                    ) select  * from tmp
                    MAP CODE to ID
                    case 1 (code has both 'LATE_CANCEL' OR FAIL and Other status )
                    - Get latest record which is NOT 'LATE_CANCEL' OR FAIL
                    Do you mean "get the *earliest* ..."? For code = 'A', both 4 and 5 are3 later than 3.
                    case 2 (code has only LATE_CANCEL or FAIL status )
                    -Get the LATEST record
                    case 3 (Code does not have any LATE_CANCEL or FAIL status)
                    Code and ID will remain same
                    Do you mean you want to show all rows in this case (e.g., code = 'C')?
                    >
                    The output should be
                    code ID
                    A    3
                    B    7
                    C    8
                    C    9
                    D    12
                    Here, For A ID is 3 because its the first record which is not 'LATE_CANCEL' OR FAIL
                    Above, you said you wanted the "latest" row.
                    for B ID IS 7 because there is no other record beside 'LATE_CANCEL' OR FAIL so we get latest record
                    for C it is both 8 and 9 because it does not have any 'LATE_CANCEL' OR FAIL records so it will remain as it is
                    for D it is 12 because it is first record which is not 'LATE_CANCEL' OR FAIL
                    This gets the results you posted:
                    WITH     got_grp    AS
                    (
                         SELECT     code, seq, id
                         ,     CASE
                                  WHEN  status IN ( 'LATE_CANCEL'
                                                  , 'FAIL'
                                            )
                                  THEN  'LC/F'
                                  ELSE  'OTHER'
                              END     AS grp
                         FROM     tmp
                    )
                    ,     got_analytics     AS
                    (
                         SELECT     code, id
                         ,     grp
                         ,     SUM ( CASE 
                                        WHEN  grp = 'LC/F'  
                                     THEN  1 
                                     ELSE  0 
                                    END
                                  ) OVER (PARTITION BY  code)          AS lc_f_cnt
                    --     ,     SUM (CASE WHEN grp = 'OTHER' THEN 1 END)     AS other_cnt
                         ,     RANK () OVER ( PARTITION BY  code
                                               ORDER BY          grp     DESC
                                          ,               CASE
                                                             WHEN  grp = 'LC/F'
                                                   THEN  -seq
                                                   ELSE  seq
                                                  END
                                        )                           AS r_num
                         FROM    got_grp
                    )
                    SELECT       code, id
                    FROM       got_analytics
                    WHERE       r_num          = 1
                    OR       lc_f_cnt     = 0
                    ORDER BY  code
                    ,            id
                    ;
                    • 7. Re: SqL query help
                      spur230
                      Many thanks to everybody who responded.That helped!
                      Learnt many cool tricks.