MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employee_leaves (`id` INTEGER, `employee_id` INTEGER, `status_id` INTEGER);

INSERT INTO employee_leaves (`id`, `employee_id`, `status_id`) VALUES
  ('1', '7', '1'), ('2', '7', '2'), ('3', '7', '4'), ('4', '7', '7'), ('9', '7', '3'),  ('27', '7', '7'),
  ('30', '8', '6'), ('31', '8', '1'), ('32', '8', '3'), ('40', '8', '4'), ('52', '8', '8'),  ('53', '8', '9'),
  ('70', '9', '2'), ('82', '9', '5'), ('93', '9', '1'), ('94', '9', '2'), ('97', '9', '6'),  ('99', '9', '1');

Records: 18  Duplicates: 0  Warnings: 0


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;


id
employee_id
status_id


4
7
7

40
8
4


WITH cte AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY employee_id
           ORDER BY FIELD(status_id, 7, 4, 3),
                    id
         ) AS rn 
  FROM employee_leaves 
  WHERE status_id IN (7, 4, 3)
)
SELECT id, employee_id, status_id 
FROM cte 
WHERE rn = 1;


id
employee_id
status_id


4
7
7

40
8
4


WITH cte AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY employee_id
           ORDER BY FIND_IN_SET(status_id, '7,4,3'),
                    id
         ) AS rn 
  FROM employee_leaves 
  WHERE status_id IN (7, 4, 3)
)
SELECT id, employee_id, status_id 
FROM cte 
WHERE rn = 1;


id
employee_id
status_id


4
7
7

40
8
4