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


  • 0
    B

    @Mr.Bin said in Department Top Three Salaries:


    Click here to see the full article post

    select Department.name as Department, tab.name 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


  • 0
    T

    Write your MySQL query statement below

    select Department.Name as Department, c.Name as Employee, c.Salary as Salary
    from(
    select b.Name Name, b.Salary Salary, b.DepartmentId DepartmentId, (
    case
    when @rl=b.DepartmentId then
    (case when @sal<>@sal:=b.Salary then @count:=@count+1
    else
    @count:=@count end
    )
    when @rl <> @rl:=b.DepartmentId then @count:=1 end
    ) num
    from (
    select @count:=0, @rl:=-1, @sal:=(select Salary from Employee order by DepartmentId, Salary desc limit 0,1) init
    ) a,
    (
    select * from Employee order by DepartmentId, Salary desc
    ) b
    ) c
    inner join Department
    on c.DepartmentId=Department.Id
    where c.num <= 3


  • 0
    X

    So windows functions are not supported here. This needs to be updated.


  • 0
    S

    my answer:

    DROP FUNCTION IF EXISTS query_min_salary_of_top3_of_depid;

    create function query_min_salary_of_top3_of_depid(depid INTEGER)
    RETURNS INTEGER
    BEGIN
    RETURN(
    select min(Salary) from (select distinct(Salary) as Salary from Employee where Employee.DepartmentId=depid order by Salary desc limit 3) as SalaryTop3

    );
    

    END ;

    select DEP_INFO.Name as Department, LEFT_EMP.Name as Employee, LEFT_EMP.Salary as Salary from
    Department as DEP_INFO join
    (
    select Name, DepartmentId,Salary from Employee as EMP where EMP.Salary >= (select query_min_salary_of_top3_of_depid(EMP.DepartmentId))
    )
    as LEFT_EMP
    on DEP_INFO.Id=LEFT_EMP.DepartmentId
    order by LEFT_EMP.DepartmentId asc, LEFT_EMP.Salary Desc


  • 0
    S
    DROP FUNCTION IF EXISTS query_min_salary_of_top3_of_depid;
    
    
    create function query_min_salary_of_top3_of_depid(depid INTEGER)
    RETURNS INTEGER
    BEGIN
        RETURN(
            select min(Salary) from (select distinct(Salary) as Salary from Employee where Employee.DepartmentId=depid order by Salary desc limit 3) as SalaryTop3
        
        );
    
    END ;
    
    select DEP_INFO.Name as Department, LEFT_EMP.Name as Employee, LEFT_EMP.Salary as Salary from
        Department as DEP_INFO join
        (
         select Name, DepartmentId,Salary from Employee as EMP where EMP.Salary >= (select query_min_salary_of_top3_of_depid(EMP.DepartmentId))
        ) 
        as LEFT_EMP
        on DEP_INFO.Id=LEFT_EMP.DepartmentId
        order by LEFT_EMP.DepartmentId asc, LEFT_EMP.Salary Desc
    

  • 0
    A

    select d.Name as Department,t.Name as Employee,Salary
    from
    (select e1.Name,e1.DepartmentId,e1.salary
    from Employee e1,Employee e2
    where e1.DepartmentId=e2.DepartmentId and e2.Salary>=e1.Salary
    group by e1.Name
    having count(distinct e2.Salary)<=3) as t
    join Department d on t.DepartmentId=d.Id;


Log in to reply
 

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