184. Department Highest Salary

---

Link

Table: Employee
id, name, salary, departmentID

Table: Department
id, name

Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in any order.

Sample

풀이

---

1. Employee 테이블에서 각 departmentID별로 가장 높은 salary 구하기

SELECT departmentID, MAX(salary)
FROM employee
GROUP BY departmentID

{"headers": ["departmentID", "MAX(salary)"], "values": [[1, 90000], [2, 80000]]}

2. 위 결과 값과 일치하는 사람들, 즉 departmentID별로 가장 높은 salary를 받고 있는 사람들만 출력하기 위해 Employee 테이블과 INNER JOIN

FROM employee e
INNER JOIN ( SELECT departmentID, MAX(salary) AS max_salary
		     FROM employee
             GROUP BY departmentID ) s ON e.departmentID = s.departmentID AND e.salary = s.max_salary

---

2번에서 틀렸던 부분

1) ON절 조건 설정
풀이 1번에서 각 departmentID 별로 Highest salary 를 출력했고,
결과로 출력된 departmentID와 salary금액 모두 일치하는 직원을 출력해야 한다.
따라서 INNER JOIN시 ON 조건에 반드시 departmentID 와 salary 조건을 모두 달아줘야 한다.

INNER JOIN ( SELECT departmentID, MAX(salary) AS max_salary
		     FROM employee
             GROUP BY departmentID ) s ON e.departmentID = s.departmentID AND e.salary = s.max_salary

---

3. Department 이름을 가져오기 위해 department 테이블과 한번 더 INNER JOIN

SELECT d.name, e.name, e.salary
FROM employee e
INNER JOIN ( SELECT departmentID, MAX(salary) AS max_salary
		     FROM employee
             GROUP BY departmentID ) s ON e.departmentID = s.departmentID AND e.salary = s.max_salary
INNER JOIN department d ON d.id = e.departmentID

My Answer

---

SELECT d.name, e.name, e.salary
FROM employee e
INNER JOIN ( SELECT departmentID, MAX(salary) AS max_salary
		     FROM employee
             GROUP BY departmentID ) s ON e.departmentID = s.departmentID AND e.salary = s.max
INNER JOIN department d ON d.id = e.departmentID