This discussion is archived
7 Replies Latest reply: Feb 12, 2013 6:50 PM by spur230 RSS

SqL query help

spur230 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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--- Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Many thanks to everybody who responded.That helped!
    Learnt many cool tricks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points