SQL - Department Highest Salary

Department Highest Salary

Medium

 

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

The Department table holds all departments of the company.

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

Explanation:

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

 

ANALYSIS:

To approach this question, I need to use dense_rank() instead of rank() to capture the ties in salary. Having said that, I emulated the dense rank().

A brief explanation for it, if departmentId is equal and the salary is the same as the previous person even after sorting them in descending order, then I give both the same rank. For the case when same department but have a different salary, (here I used > because I already sorted them in descending order) then I assigned the next rank. If a person's departmentId is different from the previous person, then it means he/she would be the person with the highest salary in his/her department. Hence, I assign 1 for the person.

SELECT 
    d.Name as Department, temp.Name as Employee, Salary
FROM    
    (SELECT 
        DepartmentId, Name, Salary, 
        @rank:= 
            CASE 
                WHEN @id = DepartmentId AND @sal = Salary THEN @rank 
                WHEN @id = DepartmentId AND @sal > Salary THEN @rank +1
                ELSE 1
            END as rank, 
        @id := DepartmentId, 
        @sal:= Salary
    FROM 
        Employee,
        (SELECT @id := null, @rank := 0, @sal:= null) f
    ORDER BY 
        DepartmentId, Salary DESC
    ) temp
    JOIN 
    Department d
    ON d.id = temp.DepartmentId
WHERE 
    rank = 1

This could be done in a differently without using dense rank:

one trick that I need to remember is to have both DepartmentId and Salary in WHERE instead of having the only Salary. Otherwise, I may pull a person from the wrong department but with same salary.

SELECT 
    d.Name as Department, e.Name as Employee, Salary
FROM 
    Employee e JOIN Department d ON d.Id = e.DepartmentId
WHERE 
    (DepartmentId,Salary) IN 
    	(
        SELECT DepartmentId, MAX(Salary) 
    	FROM Employee 
        GROUP BY DepartmentId
        )