MySQL (version lower than 8.0) : SELECT WHERE Multiple column and group by max level

Question

I have 2 tables in my database.

1. table of employees in my department. This table is stored centrally. Which I have no right to manage in this table

emp_id	name	job postion	dept_1	dept_2	dept_3
000010	emp1 name1	Director	CEO	Human resource
000012	emp2 name2	employee	CEO	Human resource
000013	emp2 name2	Director	CEO	Human resource	Recruitment
000014	emp2 name2	employee	CEO	Human resource	Recruitment
000015	emp2 name2	employee	CEO	Human resource	Recruitment
..	..	..	..	..
..	..	..	..	..
000200	emp2 name2	Head Director	CEO

question from:https://stackoverflow.com/questions/66057532/mysql-version-lower-than-8-0-select-where-multiple-column-and-group-by-max-l

Answer 1

WITH cte AS ( SELECT *, MAX(d.dept_level) OVER (PARTITION BY e.emp_id) maxlevel
              FROM employees e
              JOIN department d ON d.dept_name IN (e.dept_1, e.dept_2, e.dept_3) )
SELECT * 
FROM cte 
WHERE dept_level = maxlevel
ORDER BY emp_id

For MySQL 5.7 / MariaDB 10.1 use

SELECT *
FROM employees e
JOIN department d ON d.dept_name IN (e.dept_1, e.dept_2, e.dept_3)
JOIN ( SELECT e.emp_id,
              MAX(d.dept_level) maxlevel
       FROM employees e
       JOIN department d ON d.dept_name IN (e.dept_1, e.dept_2, e.dept_3)
       GROUP BY e.emp_id ) ed USING (emp_id)
WHERE d.dept_level = ed.maxlevel
ORDER BY emp_id

fiddle