Leetcode---Highest Salary in Department

Question

I am trying to solve the following problem: The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department. But I am wondering why the following mysql query could not get correct result.

    SELECT d.Name as Department, e.Name as Employee, e.Salary FROM Employee e
    left join Department d on e.DepartmentId=d.Id
    GROUP BY d.Name
    order by e.Salary desc limit 1

Nick · Answer 1 · 2020-07-24T06:53:48.613

1

This query will do what you want:

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM Department d
JOIN Employee e
ON e.DepartmentID = d.Id AND 
   e.Salary = (SELECT MAX(Salary) 
               FROM Employee e2
               WHERE e2.DepartmentId = d.Id);

It joins the Department table to the Employee in that department who has the highest salary for that department.

Output:

Department  Employee    Salary
Sales       Henry       80000
IT          Max         90000

Demo on SQLFiddle

edited Jul 24 '20 at 06:53

answered Aug 08 '18 at 03:37

Nick

138,499
22
57
95

This works. But why have we used "e2" here and not just "e"? – Sca Jul 24 '20 at 06:20
@Sca just to avoid confusion between the table alias in the subquery and the table alias in the main query – Nick Jul 24 '20 at 06:29
Oh okay, so "e" could be used as well? – Sca Jul 24 '20 at 06:44
@Sca yes it could. See http://www.sqlfiddle.com/#!9/40f89/170 – Nick Jul 24 '20 at 06:52
Thank you so much for clearing my doubt. just started my sql journey :) – Sca Jul 24 '20 at 06:57
@Sca no worries - good luck with your journey. – Nick Jul 24 '20 at 08:26

score 0 · Answer 2 · edited Aug 08 '18 at 03:30

0

Try this:

SET @curRank = 0;  

SELECT * FROM (

SELECT  @curRank := @curRank + 1 AS rank, salary, e.id, e.name, DepartmentId
FROM dept d
 JOIN emp e ON e.DepartmentId = d.Id 
 ORDER BY salary DESC 

) AS tbl 

GROUP BY  departmentID

This will work.

edited Aug 08 '18 at 03:30

Nick

138,499
22
57
95

answered Aug 08 '18 at 03:25

Mefenamic

38
7

Tried simple nested query but doesn't work (mysql problem): SELECT * FROM ( SELECT salary, e.id, e.name, DepartmentId FROM dept d JOIN emp e ON e.DepartmentId = d.Id ORDER BY salary DESC ) AS tbl GROUP BY departmentID – Mefenamic Aug 08 '18 at 03:28

Leetcode---Highest Salary in Department

2 Answers2