Mohamed Mohamed - 3 days ago 5
SQL Question

SQL Rank selection oracle

I have an issue such that my table looks like below;

REC_ID | Year | status |
-------|-----------|----------|
123 | 2016 | OA |
123 | 2017 | CC |


My query should check if for rec_id ? if



  1. There is a record for 2016 where the status is OA then pick that row.

  2. There is a record for 2016 where the status is any-other status (not OA) and no record exists for 2017 then pick that row (2016 row).

  3. There is a record for 2016 with any-other status (not OA) and a record exists for 2017 then pick the record for 2017



so in the above example the 2016 record should get picked up.

REC_ID | Year | status |
-------|-----------|----------|
456 | 2016 | OP |


in this example since there is only one record the 2016 record should get picked up.

REC_ID | Year | status |
-------|-----------|----------|
789 | 2016 | OM |
789 | 2017 | CC |


In this last example since there are two records and the 2016 is NOT OA then the 2017 record should get picked up.

I have tried ranking them but that doesn't work and have tried doing something like below but both records get picked up.

SELECT CASE
WHEN (STATUS = 'OA'
AND YEAR = TO_CHAR(SYSDATE, 'YYYY')
AND ?= REC_ID)
OR ((SELECT COUNT(*)
FROM TABLE
WHERE ?= REC_ID
AND YEAR = TO_CHAR(add_months(sysdate, 12), 'YYYY' ))= 0)
THEN TABLE.STATUS
ELSE
(SELECT STATUS
FROM TABLE
WHERE ?= REC_ID
AND YEAR = TO_CHAR(add_months(sysdate, 12), 'YYYY' )
)
END from TABLE WHERE ?= REC_ID ;

Answer

Re-wording the ranking:

  1. 2016 AND status = 'OA'
  2. 2017
  3. 2016

The query with ROW_NUMBER to pick the best match per rec_id:

select rec_id, year, status
from
(
  select 
    rec_id, year, status,
    row_number() OVER (partition by rec_id
               order by case 
                 when year = 2016 and status = 'OA' then 1 
                 when year = 2017 then 2
                 else 3
               end) as rn
  from mytable
  where year in (2016, 2017)
)
where rn = 1;
Comments