184 Department Highest Salary

1. 문제

The Employee table holds all employees. Every employee has an id, a salary, and there is also a column for the department id.

The Department table holds all departments of the company

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows(order of rows does not matter).

Explanation: Max and Jom both have the highest salary in the IT department and Henry has the highest salary in the Sales department

2. 풀이

• Case문으로 1인 경우 IT, 2인 경우 Sales로 바꿔준다.
• Group by로 max값을 뽑으면 IT 부서에서 Jim만 뽑힌다. Inner Join을 통해 Jim, Max 모두 뽑아야 한다.
  • Departmentid, Salary의 max값 두 컬럼을 추출한 후 원래 raw table에 inner join한다.

SELECT
	CASE
		WHEN e.departmentid = 1 THEN 'IT'
		WHEN e.departmentid = 2 THEN 'Sales'
	END AS Department,
	e.Name AS Employee,
	e.Salary AS Salary
FROM
	employee AS e
INNER JOIN (
	SELECT
		DepartmentId,
		MAX(Salary) AS max_salary
	FROM
		EMPLOYEE
	GROUP BY
		DepartmentId) AS sub ON
	e.DepartmentId = sub.DepartmentId
	AND e.salary = sub.max_salary;