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


  • 4

    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

  • 0

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

    BTW would you plz explain it a bit?


  • 0

    @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


Log in to reply
 

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