Finding MAX after 2 JOINs between 3 different tables

Question

I have 3 tables employees, salaries and dept_emps in below given format:

Employees:

emp_no     first_name
---------------------
01         ABC
02         XYZ
03         MNO
04         PQR

Salaries:

emp_no     salary
-------------------
01          10000
02          20000
03          15000
04         100000

dept_emp:

emp_no     dept_no
------------------
01         d01
02         d02
03         d01
04         d02

Required output:

dept_no and emp_id of employee with highest salary in that department

dept_no    emp_id
------------------
d01        03
d02        04

I tried using filtering with a subquery, but it got stuck like a infinite loop without output:

SELECT first_name, dept_no
FROM employees e 
JOIN salaries s USING (emp_no)
JOIN dept_emp de USING (emp_no)
WHERE s.salary = (SELECT MAX(salary)
                  FROM dept_emp de2
                  JOIN salaries USING (emp_no)
                  WHERE de2.dept_no = de.dept_no);

What is the efficient query for this and why is my query not working?

Answer 1

You need an extra nested query with aggregation that calculates maximum salaries per each department such as

SELECT ss.dept_no, s.emp_no as emp_id
  FROM salaries s
  JOIN
   (
    SELECT d.dept_no, MAX(s.salary) AS max_salary
      FROM salaries s 
      JOIN dept_emp d USING (emp_no)
     GROUP BY d.dept_no 
   ) ss
    ON s.salary = ss.max_salary

P.S: employees table is not needed

Demo

If your DB's version is 8, then use an analytic function such as DENSE_RANK() within a subquery :

SELECT dept_no, emp_no AS emp_id
  FROM
  (
    SELECT d.dept_no, s.emp_no, 
           DENSE_RANK() OVER (PARTITION BY d.dept_no ORDER BY s.salary DESC) AS dr
      FROM salaries s 
      JOIN dept_emp d 
        ON d.emp_no = s.emp_no
  ) sd     
 WHERE dr = 1

Demo