SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary FROM Employee E, (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) T, Department D WHERE E.DepartmentId = T.DepartmentId AND E.Salary = T.max AND E.DepartmentId = D.id SELECT D.Name,A.Name,A.Salary FROM Employee A, Department D WHERE A.DepartmentId = D.Id AND NOT EXISTS (SELECT 1 FROM Employee B WHERE B.Salary > A.Salary AND A.DepartmentId = B.DepartmentId) SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary from Employee E, Department D WHERE E.DepartmentId = D.id AND (DepartmentId,Salary) in (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId)
optimize the first solution, reduce a table join.
SELECT d1.Name , e1.Name , e1.Salary FROM ( SELECT d.Id , MAX(d.Name) AS Name , MAX(e.Salary) AS Salary FROM Department d JOIN Employee e ON e.DepartmentId = d.Id GROUP BY d.Id ) d1 JOIN Employee e1 ON d1.Id = e1.DepartmentId AND e1.Salary = d1.Salary
Simply using 'WHERE' clauses instead of 'JOIN's might cause your query results to vary depending on your database engine and query optimizer (click for stack overflow).
I use Employee.Id to link M to E, why it does not work as using DepartmentId to link them?
SELECT D.Name AS Department, E.Name AS Employee, E.Salary FROM (SELECT Id, Max(Salary) AS Salary FROM Employee GROUP BY DepartmentId) M JOIN Employee E ON E.Id = M.Id JOIN Department D ON E.DepartmentId = D.Id WHERE M.Salary = E.Salary
In your Derived table M, you are grouping the table on DepartmentId, but you Select the Column Id without any Aggregate Function.
SELECT D.Name AS Department, E.Name AS Employee, E.Salary FROM (SELECT DepartmentId, Max(Salary) AS Salary FROM Employee GROUP BY DepartmentId) M JOIN Employee E ON E.DepartmentId = M.DepartmentId JOIN Department D ON E.DepartmentId = D.Id WHERE M.Salary = E.Salary; ;
This should work, also you may put the final where clause of Salary as a joining condition on between E and M, as it filters out unwanted records as fast as possible.
Hope this helps, Cheers!!
I am trying to modify your 3rd solution to the above one. Is there any issue with this?
select d.Name as Department,rr.Name as Employee,rr.Salary
(select e.Name,e.Salary,e.DepartmentId from Employee e,(SELECT Salary,DepartmentId from Employee group by DepartmentId having count(*) = 1 or max(Salary)) r where e.DepartmentId = r.DepartmentId and e.Salary = r.Salary) rr,Department d
where d.id = rr.DepartmentId
why this wrong
This solution using subquery is not optimal!
Pls check out my solution without subquery, max function and follow-up questions:)
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.