Rank function with complex scenario

Question

I have a TAble TABLE1 and having columns like ID, Status and Code

BAsed on the code priority i want the output , the priority is

SS -> RR - > TT - > AA ( these priority is not stored in any tables)

Query should first look for Approved status then we need to check for Code column Example1: ID: 2345 - This record having Approved status for all the codes like SS , AA and RR and based on the code priority SS should be pulled in the output as 2345, SS

Example2: ID: 3333- This record having Approved status for all the codes like RR and TT and based on the code priority RR should be pulled in the output as 3333, RR

ID: 4444- Eventhough this record is having Codes like SS and RR but it is status column is having value as TERMED so we need to populate the next priority in the list and output should display as 4444 TT

ID: 5555- None of the status for this ID is having Approved status all are having status as Termed so based on the priority in the output 5555,SS should be picked as this one is the priority

so output for 2345 and 5555 is same only the difference is if none of the record having approved status then only we should go for Termed - if the record is only having termed then based on priority record should be pulled

Attached the picture for reference

Answer 1

You may use RANK along with a CASE expression for ordering:

WITH cte AS (
    SELECT t.*,
           RANK() OVER (PARTITION BY ID
                        ORDER BY CASE Status WHEN 'Approved' THEN 1
                                             WHEN 'Termed'   THEN 2
                                             ELSE 3 END,
                                 CASE Code WHEN 'SS' THEN 1
                                           WHEN 'RR' THEN 2
                                           WHEN 'TT' THEN 3
                                           WHEN 'AA' THEN 4 END) rnk
    FROM yourTable t
    WHERE Status = 'Approved'
)

SELECT ID, Code
FROM cte
WHERE rnk = 1;

Demo

Answer 2

create table table1 (id, status, code) as
select 2345, 'Approved', 'SS' from dual union all
select 2345, 'Approved', 'AA' from dual union all
select 2345, 'Approved', 'RR' from dual union all
select 3333, 'Approved', 'RR' from dual union all
select 3333, 'Approved', 'TT' from dual union all
select 4444, 'TERMED', 'SS' from dual union all
select 4444, 'TERMED', 'RR' from dual union all
select 4444, 'Approved', 'TT' from dual
;

select ID, CODE
from (
  select ID, STATUS, CODE
    , row_number()over( 
            partition by ID 
            order by status
                     , decode(code, 'SS', 1, 'RR', 2, 'TT', 3, 'AA', 4) ) rank
  from table1
)
where rank = 1
;