Two solutions: 1) count + join; 2) Three variables + join


  • 1
    J
    1. count + join:

      select d.Name as Department, e.Name as Employee, e.Salary from Employee as e
      inner join Department d on e.DepartmentId = d.Id
      where (select count(distinct e1.Salary) from Employee e1 where e1.Salary > e.Salary
      and e1.DepartmentId = e.DepartmentId) < 3
      order by d.Name, e.Salary DESC;

    1. Three variables + join :

      select d.Name Department, t.Name Employee, t.Salary
      from (select Name, Salary, DepartmentId,
      @rank := IF(@prev_department = DepartmentId, @rank + (@prev_salary <> Salary), 1) AS rank,
      @prev_department := DepartmentId,
      @prev_salary := Salary
      from Employee, (select @prev_department := -1, @prev_salary := 0.0, @rank := 1) as init
      ORDER BY DepartmentId, Salary DESC) t
      inner join Department d on d.Id = t.DepartmentId
      where t.rank <= 3 ORDER BY d.Name, t.Salary DESC;


  • 0
    S

    Hello! Could you please explain the 1st method (count+join)?


Log in to reply
 

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