Department Top Three Salaries


  • 0

    Click here to see the full article post


  • 0
    A

    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 =a2.id
    ) a
    where rownum<=3;


  • 0
    I

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

    select
    Department.name as Department,
    empl.name as Employee,
    empl.salary as Salary
    from
    (select
    @row_number := case
    when(@dep_id = DepartmentId) then @row_number+1 else 1 end as row_no,
    @dep_id:=DepartmentId as dep_id,
    Name,
    Salary
    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


Log in to reply
 

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