My Answer about this seems right, but can't pass: Department Top Three Salaries


  • 0
    K

    Below is my answer about this question, although it's ugly but I think it's right.
    https://leetcode.com/problems/department-top-three-salaries/

    SELECT d2.name as Department, e2.Name as Employee, e2.Salary
    FROM
    Employee as e2 join Department as d2 on e2.Departmentid = d2.Id
    Where e2.Id in
    (
    select Id from
    (Select Id, RANK, CLASS from
    (SELECT Id,
    @employee:=CASE WHEN @dep <> DId THEN 1 ELSE @employee+1 END AS RA, @employee as RANK,
    @dep:=Did AS CLASS
    FROM
    (SELECT @employee:= 0) AS em,
    (SELECT @dep:= 0) AS c,
    (Select e.Id as Id, e.DepartmentId as Did from Employee as e
    #where @employee < 4
    order by e.DepartmentId, e.Salary desc
    ) AS temp1
    ) AS temp2
    where RANK < 4) as temp3
    )
    order by e2.DepartmentId, e2.Salary desc


  • 0
    K

    I know what's wrong. Question is about top 3 salaries, not the top 3 people who have the highest salaries.


  • 1
    K

    Accepted after script changes with new understanding:

    SELECT d2.name as Department, e2.Name as Employee, e2.Salary
    FROM
    Employee as e2 join Department as d2 on e2.Departmentid = d2.Id
    Where e2.Id in
    (
    select Id from
    (Select Id, RANK, CLASS from
    (SELECT Id,
    @rank:=CASE WHEN @dep <> DId THEN 1 ELSE CASE WHEN @sal = Salary THEN @rank ELSE @rank+1 END END AS rk,
    @sal:=CASE WHEN @sal <> Salary THEN Salary ELSE @sal END AS CURSAL,
    @rank as RANK,
    @dep:=Did AS CLASS
    FROM
    (SELECT @rank:= 0) AS em,
    (SELECT @dep:= 0) AS c,
    (SELECT @sal:= 0) AS s,
    (Select e.Id as Id, e.DepartmentId as Did, e.Salary from Employee as e
    order by e.DepartmentId, e.Salary desc
    ) AS temp1
    ) AS temp2
    where RANK < 4) as temp3
    )
    order by e2.DepartmentId, e2.Salary desc


Log in to reply
 

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