Submit your solution: https://leetcode.com/problems/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) —
GD Star Rating
loading...
333 wordsloading...
Last Post: Counting Number of Set Bits for N Integers using Dynamic Programming Algorithm in Linear Time
Next Post: C++ Coding Exercise - Palindrome Pairs