Department Top Three Salaries

  • 0

    Click here to see the full article post

  • 0

    select *
    from (select a2.Name as Department,a1.Name as Employee,salary,
    row_number () over (partition by a2.Name order by salary desc) as rownum
    from Employee a1
    join Department a2 on a1.Id
    ) a
    where rownum<=3;

  • 0

    row_number() is not supported in MySQL, however we can use session variable to implement it:

    select as Department, as Employee,
    empl.salary as Salary
    @row_number := case
    when(@dep_id = DepartmentId) then @row_number+1 else 1 end as row_no,
    @dep_id:=DepartmentId as dep_id,
    from Employee, (select @row_number:=0, @dep_id:=0) as temp
    order by DepartmentId, salary DESC
    ) empl
    left join Department on empl.dep_id = Department.Id
    where empl.row_no <= 3

  • 0

    @Mr.Bin said in Department Top Three Salaries:

    Click here to see the full article post

    select as Department, as Employee, tab.salary as Salary from (
    select * from (
    select name, DepartmentId, max(salary) salary, count(distinct x) as rank from (
    select e1.*, e2.salary as x from Employee e1 join Employee e2 on e1.Salary <= e2.Salary and e1.DepartmentId = e2.DepartmentId
    ) t group by name, DepartmentId
    ) t1 where rank <= 3
    ) tab join Department on tab.DepartmentId = Department.Id

Log in to reply

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