Here is my solution, but somehow this is not accepted
SELECT Department.Name Department, Employee.Name Employee, Salary FROM Employee JOIN Department ON DepartmentId = Department.Id GROUP BY DepartmentId HAVING MAX(Salary)
Can somebody explain why this is not accepted?
I don't know!
But I'm having a similar issue. It'd be great if some of the more knowledgeable folks can reply.
I don't understand why there needs to be a subquery.
This is mine:
select d1.Name as Department, e1.Name as Employee, max(e1.Salary) as Salary from Department as d1 JOIN Employee as e1 ON e1.DepartmentId=d1.Id Group by d1.Id
I think I got it.
With the code that I posted, it only shows one person for each department even with the case when there are more than two people have a max salary.
Cool, yeah I had the same problem, and so needed to do a sub query. This finally worked:
select d1.Name as Department, e2.Name as Employee, e2.salary as Salary from Department as d1 JOIN Employee as e2 on e2.DepartmentId=d1.Id JOIN (select e1.Name as Employee, max(e1.Salary) as maxSalary, e1.DepartmentId from Employee as e1 Group by e1.DepartmentId) as T On T.DepartmentId=e2.DepartmentId Where e2.salary=T.maxSalary
Also i believe Having needs to be a conditional statement? So something like
Having aggregate > 50 or something
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.