SELECT b.Name as Department, a.Name as Employee, a.Salary FROM Employee a JOIN Department b ON a.DepartmentId = b.Id GROUP BY Department HAVING a.Salary = max(a.Salary)
This way it was not able to return multiple rows with same highest salary. I can't figure why, please help!
I have the exact same problem and my query is very similar to yours
select Department.Name as Department, Employee.Name as Employee, Employee.Salary as Salary from Employee right join Department on Employee.DepartmentId = Department.Id group by Department.Id having Employee.salary = max(Employee.salary)
After checking the correct solutions posted by other people. I think there might be something to do with the group by? It seems to me it only produces one record for each group.
To overcome this issue, we have to join the result table with the original table again.
Here's the final query:
select tmp.Department as Department, e1.Name as Employee, e1.Salary as Salary from Employee e1 right join ( select d.Id as Id, d. Name as Department, max(e.salary) as Salary from Employee e right join Department d on e.DepartmentId = d.Id group by d.Id having Salary = max(e.salary) ) tmp on e1.Salary = tmp.Salary and e1.DepartmentId = tmp.Id
The rows in the tmp table is the ones you generated I just included Id field into the tmp table for the first right join
you use nonaggregated property in the select
select d.Name as Department, e.Name as Employee, Salary from Department as d join Employee as e on d.Id = e.DepartmentId where (e.DepartmentId, Salary) in (select DepartmentId, max(Salary) from Employee group by DepartmentId)
For the example dataset,
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+
If you run the following query you will get the first row ["Joe", 70000]. The reason is that MySQL returns the first row in each department that meets the HAVING clause condition.
SELECT Name, Salary FROM Employee GROUP BY DepartmentId HAVING MAX(Salary) = 90000 ;
When you run the next query, it only returns the record ["Henry", 80000]. The reason is that for department 2, the first record is for "Henry" and his salary 8000 matches the department's max salary 8000. For department1, however, the first record is for "Joe" as shown above and his salary 7000 does not match the department's max salary 9000.
SELECT Name, Salary FROM Employee GROUP BY DepartmentId HAVING MAX(Salary) = Salary ;
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.