Selecting single row for each employee with status_id by priroity

Question

I need to extract only one record for each employee (employee_id) from employee_leaves table but only if status_id is equal to one of the following numbers: 7, 4, 3 (written by priority). When row with status_id=7 has not been found, it should look for a row with status_id=4. When not found, it should look for row with status_id=3.

Example: row with id=4 should be returned

id	employee_id	status_id
1	7	1
2	7	2
3	7	4
4	7	7
9	7	3
27	7	7

Answer 1

First filter the rows of each employee_id for the status_ids that you want.
Then rank the rows with ROW_NUMBER() window function by applying the priorities that you want.
Finally pick the first row from each ranking:

WITH cte AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY employee_id
           ORDER BY status_id = 7 DESC,
                    status_id = 4 DESC,
                    status_id = 3 DESC, -- this is the last option and it is actually not needed
                    id
         ) AS rn 
  FROM employee_leaves 
  WHERE status_id IN (7, 4, 3)
)
SELECT id, employee_id, status_id 
FROM cte 
WHERE rn = 1;

The ORDER BY clause inside ROW_NUMBER() may be simplified with the use of function FIELD():

ORDER BY FIELD(status_id, 7, 4, 3),
         id

or the function FIND_IN_SET():

ORDER BY FIND_IN_SET(status_id, '7,4,3'),
         id

See the demo.