SQL Coding Exercise – Find Department Highest Salary

Submit your solution: https://leetcode.com/problems/department-highest-salary/

leetcode-sql-exercise-find-department-highest-salary

As seen, two tables, you are asked to list the highest salary for each department and the employee name. However, if there are two or more employees in the same department having the same salary, you need to output all of them.

Using Max function

The core nested SQL is to group the salary by each department and use the Max function to obtain the highest one.

(select DepartmentId as ID, Max(Salary) as M from Employee group by DepartmentId) as T

Then we can just join the two tables, and this nested ‘table’:

select 
    Department.Name as Department,
    Employee.Name as Employee,
    T.M as Salary
from
    Employee,
    Department,
    (select DepartmentId as ID, Max(Salary) as M from Employee group by DepartmentId) as T
where
    Employee.Salary = T.M and
    Department.Id = T.ID and 
    Employee.DepartmentId = Department.Id

Use All function

If the salary is greater or equal to all salaries in the department, then we find the highest one.

select 
    Department.Name as Department, 
    e1.Name as Employee, 
    Salary
from 
    Employee e1, 
    Department
where 
    e1.DepartmentId = Department.Id 
    and
    Salary >= ALL (select Salary from Employee e2 where e2.DepartmentId = e1.DepartmentId);

This solution is slightly slower (1258ms) than the first one (1025ms).

–EOF (The Ultimate Computing & Technology Blog) —