LeetCode 184. Department Highest Salary SQL Solution

Problem

LeetCode SQL Problem

184. Department Highest Salary

Employee table

Id	Name	Salary	DepartmentId
1	Joe	70000	1
2	Jim	90000	1
3	Henry	80000	2
4	Sam	60000	2
5	Max	90000	1

Department table

Id	Name
1	IT
2	Sales

Solution - Rank() Window Function

MySQL

Department Highest Salary

-- Rank salary in each department. Highest salary is ranked as 1.
WITH SalaryRankedByDept
AS (
	SELECT D.Name AS Department
		,E.Name AS Employee
		,E.Salary
		,rank() OVER (
			PARTITION BY D.Id ORDER BY E.Salary DESC
			) AS RankNum
	FROM Department AS D
	INNER JOIN Employee AS E ON D.Id = E.DepartmentId
	)
-- Output highest ranked salary in each department
SELECT Department
	,Employee
	,Salary
FROM SalaryRankedByDept
WHERE RankNum = 1

TSQL

Department Highest Salary

-- Rank salary in each department. Highest salary is ranked as 1.
WITH SalaryRankedByDept
AS (
	SELECT D.Name AS Department
		,E.Name AS Employee
		,E.Salary
		,rank() OVER (
			PARTITION BY D.Id ORDER BY E.Salary DESC
			) AS RankNum
	FROM Department AS D
	INNER JOIN Employee AS E ON D.Id = E.DepartmentId
	)
-- Output highest ranked salary in each department
SELECT Department
	,Employee
	,Salary
FROM SalaryRankedByDept
WHERE RankNum = 1

Query Output

Name	Name	Salary
Sales	Henry	80000
IT	Max	90000
IT	Jim	90000

Solution - Group By

MySQL

Department Highest Salary

-- Find the highest salary for each department
WITH MaxSalaryByDept
AS (
	SELECT DepartmentId
		,max(Salary) AS MaxEmpSalary
	FROM Employee
	GROUP BY DepartmentId
	)
-- Select employees who earn the highest salary in his/her department
SELECT D.Name
	,E.Name
	,E.Salary
FROM Department AS D
INNER JOIN Employee AS E ON D.Id = E.DepartmentId
INNER JOIN MaxSalaryByDept AS MS ON D.Id = MS.DepartmentId
	AND E.Salary = MS.MaxEmpSalary

TSQL

Department Highest Salary

-- Find the highest salary for each department
WITH MaxSalaryByDept
AS (
	SELECT DepartmentId
		,max(Salary) AS MaxEmpSalary
	FROM Employee
	GROUP BY DepartmentId
	)
-- Select employees who earn the highest salary in his/her department
SELECT D.Name
	,E.Name
	,E.Salary
FROM Department AS D
INNER JOIN Employee AS E ON D.Id = E.DepartmentId
INNER JOIN MaxSalaryByDept AS MS ON D.Id = MS.DepartmentId
	AND E.Salary = MS.MaxEmpSalary

Query Output

Name	Name	Salary
Sales	Henry	80000
IT	Max	90000
IT	Jim	90000