Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    18

    Unanswered: Fetch records based on status priority

    I have a table with below syntax and insert statements.

    -- Table creation
    -----------------------
    create table order_test (oid number,
    orderno number,
    ordername varchar2(100),
    orderstatus varchar2(50),
    orderseq number);
    ----------------------------------
    -- Insert statements
    begin
    insert into order_test values (1,100,'ORD1','INPROGRESS',1);
    insert into order_test values (2,101,'ORD2','INPROGRESS',2);
    insert into order_test values (3,100,'ORD1','START',2);
    insert into order_test values (4,100,'ORD1','INPROGRESS',3);
    insert into order_test values (5,101,'ORD2','COMPLETE',1);
    insert into order_test values (6,101,'ORD2','CANCELLED',3);
    insert into order_test values (7,100,'ORD1','START',4);
    insert into order_test values (8,102,'ORD3','COMPLETE',1);
    insert into order_test values (9,103,'ORD4','CANCELLED',1);
    insert into order_test values (10,103,'ORD4','CANCELLED',2);
    commit;
    end;
    The Output looks like below
    OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
    1 |100 |ORD1 |INPROGRESS |1
    7 |100 |ORD1 |START |4
    3 |100 |ORD1 |START |2
    4 |100 |ORD1 |INPROGRESS |3
    6 |101 |ORD2 |CANCELLED |3
    2 |101 |ORD2 |INPROGRESS |2
    5 |101 |ORD2 |COMPLETE |1
    8 |102 |ORD3 |COMPLETE |1
    9 |103 |ORD4 |CANCELLED |1
    10 |103 |ORD4 |CANCELLED |2
    Now, the below rules should be applied to get the output..
    1) If status is same, then fetch max(seq) record
    2) If status is different, then apply below priority to fetch the records
    Start - Priority 1 (High)
    In Progress - Priority 2
    Cancelled - Priority 3
    Complete - Priority 4 (Lowest)

    Means, the output for each order should look like below.
    OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
    3 |100 |ORD1 |START |2
    2 |101 |ORD2 |INPROGRESS |2
    8 |102 |ORD3 |COMPLETE |1
    10 |103 |ORD4 |CANCELLED |2
    Can somebody please suggest the ways how to do it.

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't understand why ORDERNO 100 results in that record (having the OID = 3), and not OID = 7. Because, both OID = 3 and OID = 7 have the same ORDERSTATUS = START, but different ORDERSEQ. Your rule #1 says that you should take MAX(SEQ) (which is 4, for OID = 7, not 2 for OID = 3). So?

  3. #3
    Join Date
    Jun 2012
    Posts
    18

    Fetch records based on status priority

    Sorry its a typo.

    Actually it should be ID 7 for order no 100 with Seq as 4.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here you go:
    Code:
    SQL> with ord as
      2    (select oid, orderno, ordername, orderstatus, orderseq,
      3       rank() over (partition by orderno
      4                    order by case when orderstatus = 'START' then 1
      5                                  when orderstatus = 'INPROGRESS' then 2
      6                                  when orderstatus = 'CANCELLED' then 3
      7                                  when orderstatus = 'COMPLETE' then 4
      8                             end,
      9                             orderseq desc
     10                      ) rn
     11       from order_test
     12    )
     13  select oid, orderno, ordername, orderstatus, orderseq
     14  from ord
     15  where rn = 1
     16  order by orderno;
    
           OID    ORDERNO ORDERNAME       ORDERSTATUS       ORDERSEQ
    ---------- ---------- --------------- --------------- ----------
             7        100 ORD1            START                    4
             2        101 ORD2            INPROGRESS               2
             8        102 ORD3            COMPLETE                 1
            10        103 ORD4            CANCELLED                2
    
    SQL>

Posting Permissions

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