If first condition satisfies perform it, otherwise second condition in Oracle

Question

I am trying to take Employee by status from table. I have 2 statuses, If an employee has A condition take that row, otherwise take P status row with maximum oper_day It looks like below:

Table

---------------------------------------------------
id  |  emp_code  | name    | status   | oper_day  |
--------------------------------------------------
1   |  164094    | John    | P        | 2020-10-02 |
2   |  164094    | John    | P        | 2020-10-09 |
3   |  164094    | John    | A        | 2020-10-10 |
4   |  145890    | Mike    | P        | 2020-10-05 |

My result should look like below

--------------------------------
id  |  emp_code  | name    | status   | oper_day  |
--------------------------------------------------
1   |  164094    | John    | A        | 2020-10-10 |
2   |  145890    | Mike    | P        | 2020-10-05 |

Any help is appreciated

Answer 1

Using ROW_NUMBER:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY emp_code ORDER BY status, oper_day DESC) rn
    FROM yourTable t
)

SELECT id, emp_code, name, status, oper_day  
FROM cte
WHERE rn = 1;

The logic here is that should an employee have a status A record, it would be assigned the first row number, since A sorts before P. Otherwise, a P status record would be chosen. We choose the more recent record per employee in case of multiple records.