# Three accpeted solutions

• ``````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)``````

• This post is deleted!

• mine was same as last one as yours.. awesome :)

• This post is deleted!

• mine is the same as the third one as well:)

• the 3rd one result in: Time Limit Exceeded

• I like the first one, easy to understand

• 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
``````

• @lichenyu2014

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!!
;

• SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary
FROM Employee E JOIN Department D
WHERE (DepartmentId,Salary) in
(SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId)

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
from
(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:)
https://discuss.leetcode.com/topic/108599/my-best-solution-super-clean-no-subquery-no-max/2

• @jyoun44 said in Three accpeted solutions:

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 agree

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.