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;


  • 0
    N

    Solution using postgresql's lateral join:

    select
        department.Name department,
        lat.Name employee,
        lat.Salary salary
      from department
        left join lateral
        (
            select
                employee.Name,
                employee.Salary
              from employee
              where employee.DepartmentId = department.Id
              order by employee.Salary desc
              limit 3
        ) lat on true
    ;
    

  • 0
    G

    SELECT department,employee,SALARY
    FROM(
    SELECT
    dense_rank()over(PARTITION BY Department order by salary desc) as RANKID,*
    from
    (
    SELECT department.name as department ,employee.name as employee,salary
    from Department
    left join Employee
    on department.id = employee.departmentid
    ) AS TT
    ) AS TTT
    WHERE RANKID<=3


  • 0
    R

    Write your MySQL query statement below

    set @rnk=1;
    set @pv='';
    set @sal=-1;
    set @val=1;
    select Department,Employee,salary
    from
    (select Department,Employee,salary,
    @rnk := if(@pv=Department,if(@sal=salary,@rnk,@rnk+1),1) as rank,
    @pv := Department,
    @sal := salary
    from
    (select dept.name as Department,emp.name as Employee,Salary from Employee emp inner join department dept on emp.DepartmentId=dept.id order by dept.name asc,Salary desc) A) B where rank<=3


  • 0
    S

    I cannot get the result below when running the first part:

    Employee Salary
    Henry 80000
    Max 90000
    Randy 85000

  • 0
    C

    This should work :-

    select
    b.name as departement,
    a.name as employee,
    a.salary as salary
    from
    (select
    name ,
    departmentid ,
    salary ,
    rank over() (partition by departmentid order by salary desc) rn
    from employee )a , departement b
    where a.departmentid = b.id
    and a.rn <= 3;


  • 0
    Y

    T-SQL or SQL Server solution:
    select x.Department, x.Employee, x.salary
    from
    (
    select b.Name as 'Department', a.Name as 'Employee', salary, DepartmentId, dense_rank() over (partition by a.DepartmentId order by salary desc) as Rank
    from #TempTable1 a
    inner join #TempTable2 b on a.DepartmentId = b.Id
    ) x
    where x.Rank <= 3


  • 0
    T

    The query proposed as a solution:

    SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
    FROM
    Employee e1
    JOIN
    Department d ON e1.DepartmentId = d.Id
    WHERE
    3 > (SELECT
    COUNT(DISTINCT e2.Salary)
    FROM
    Employee e2
    WHERE
    e2.Salary > e1.Salary
    AND e1.DepartmentId = e2.DepartmentId
    );

    returns
    +------------+----------+--------+
    | Department | Employee | Salary |
    +------------+----------+--------+
    | IT | Joe | 70000 |
    | Sales | Henry | 80000 |
    | Sales | Sam | 60000 |
    | Sales | Henry | 80000 |
    | Sales | Sam | 60000 |
    | IT | Max | 90000 |
    | IT | Randy | 85000 |
    +------------+----------+--------+
    7 rows in set (0.00 sec)

    You need to add DISTINCT and ORDER BY to obtain the result requested:

    SELECT DISTINCT r.Department, r.Employee, r.Salary
    FROM (
    SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary AS Salary
    FROM
    Employee e1
    JOIN
    Department d ON e1.DepartmentId = d.Id
    WHERE
    3 > (SELECT COUNT(DISTINCT e2.Salary)
    FROM
    Employee e2
    WHERE
    e2.Salary > e1.Salary
    AND e1.DepartmentId = e2.DepartmentId
    )
    ) r
    ORDER BY r.Department, r.Salary DESC;

    and it returns:
    +------------+----------+--------+
    | Department | Employee | Salary |
    +------------+----------+--------+
    | IT | Max | 90000 |
    | IT | Randy | 85000 |
    | IT | Joe | 70000 |
    | Sales | Henry | 80000 |
    | Sales | Sam | 60000 |
    +------------+----------+--------+
    5 rows in set (0.00 sec)

    Hope it helps ;-) I ran on MySQL version 5.7.17-log


  • 0
    T

    The query proposed as a solution:

    SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
    FROM
    Employee e1
    JOIN
    Department d ON e1.DepartmentId = d.Id
    WHERE
    3 > (SELECT
    COUNT(DISTINCT e2.Salary)
    FROM
    Employee e2
    WHERE
    e2.Salary > e1.Salary
    AND e1.DepartmentId = e2.DepartmentId
    );
    

    returns
    +------------+----------+--------+
    | Department | Employee | Salary |
    +------------+----------+--------+
    | IT | Joe | 70000 |
    | Sales | Henry | 80000 |
    | Sales | Sam | 60000 |
    | Sales | Henry | 80000 |
    | Sales | Sam | 60000 |
    | IT | Max | 90000 |
    | IT | Randy | 85000 |
    +------------+----------+--------+
    7 rows in set (0.00 sec)

    You need to add DISTINCT and ORDER BY to obtain the result requested:

    FROM (
    SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary AS Salary
    FROM
    Employee e1
    JOIN
    Department d ON e1.DepartmentId = d.Id
    WHERE
    3 > (SELECT COUNT(DISTINCT e2.Salary)
    FROM
    Employee e2
    WHERE
    e2.Salary > e1.Salary
    AND e1.DepartmentId = e2.DepartmentId
    )
    ) r
    ORDER BY r.Department, r.Salary DESC;
    

    and it returns:
    +------------+----------+--------+
    | Department | Employee | Salary |
    +------------+----------+--------+
    | IT | Max | 90000 |
    | IT | Randy | 85000 |
    | IT | Joe | 70000 |
    | Sales | Henry | 80000 |
    | Sales | Sam | 60000 |
    +------------+----------+--------+
    5 rows in set (0.00 sec)

    Hope it helps ;-) I ran on MySQL version 5.7.17-log


  • 0
    C

    Better output format this way:

    SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
    FROM Employee e1
    JOIN Department d
    ON e1.DepartmentId = d.Id
    WHERE (SELECT COUNT(DISTINCT e2.Salary)
    FROM Employee e2
    WHERE e2.Salary > e1.Salary
    AND e1.DepartmentId = e2.DepartmentId
    ) < 3
    ORDER BY 1, 3


  • 0
    A

    I try to through out some way, but something I confuse
    my code is
    select d.name as Department, e.name as Employee, e.salary as Salary
    from employee e
    join department d on e.departmentid = d.id
    where
    (
    select
    count(distinct e2.salary)
    from
    employee e2
    where e2.salary > e.salary and e2.departmentid = e.departmentid
    ) < 3
    order by e.salary DESC
    but why after second select plus count?
    i try to remove it but it's wrong.
    can anyone tell me why i have to do it?


  • 0
    S

    Solution based on window functions. Tested on Oracle SQL.

    with agg as (
    select DepartmentId, Name, Salary, dense_rank() over (partition by DepartmentId order by Salary desc) as rank
    from Employee)
    
    select d.Name as "Department", a.Name as "Employee", a.Salary as "Salary"
    from agg a 
    join Department d on a.DepartmentId = d.Id
    where a.rank < 4
    

Log in to reply
 

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