Clash Royale CLAN TAG#URR8PPP

Leetcode---Highest Salary in Department [duplicate]

This question already has an answer here:

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


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers
2

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


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.