My best solution, super clean, no subquery, no Max

• Oftentimes those interviewers won't allow you to write subquery~

Return the highest salary for each department

``````  SELECT D.Name as Department, E.Name as Employee, E.Salary
FROM Department D, Employee E, Employee E2
WHERE D.ID = E.DepartmentId and E.DepartmentId = E2.DepartmentId and
E.Salary <= E2.Salary
group by D.ID,E.Name having count(distinct E2.Salary) = 1
order by D.Name desc
``````

Follow up, return the secondary salary for each department

``````   SELECT D.Name as Department, E.Name as Employee, E.Salary
FROM Department D, Employee E, Employee E2
WHERE D.ID = E.DepartmentId and E.DepartmentId = E2.DepartmentId and
E.Salary < E2.Salary
group by D.ID,E.Name having count(distinct E2.Salary) = 1
order by D.Name desc``````

• @tongzhou2 I think the first one is returning the highest salary instead of the secondary :P

BTW would you plz explain it a bit?

• @Kara Hi thank you for the correction.

The idea is simple, suppose we have Employee table looks like:

``````DepartmentId    Name   Salary
1            John      3
1            Sam       2
1            Andy      1
``````

We have to use self join on Employee table to pair each individual's salary with those who's salary is higher or equal to him:

``````   departmentID     E.name      E.Salary    E2.name     E2.Salary
1       John          3          John            3
1       Sam           2          John            3
1       Sam           2          Sam             2
1       Andy          1          John            3
1       Andy          1          Sam             2
1       Andy          1          Andy            1
``````

Then group by departmentID,Name having count(distinct E2.Salary) = 1 will give you the one who has the highest salary within his department.

The same logic works for question regarding the secondary/third/top three/top five salary for each department

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.