BiruLyu
6/1/2017 - 5:18 PM

184. Department Highest Salary(1st).sql

# Write your MySQL query statement below
SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary
FROM Employee AS E 
JOIN  Department AS D ON E.DepartmentId = D.Id
JOIN  (SELECT E2.DepartmentId AS DepartmentId, MAX(E2.Salary) AS maxSalary
                    FROM Employee AS E2
                    GROUP BY E2.DepartmentId) AS T ON E.Salary = T.maxSalary and T.DepartmentId = E.DepartmentId

# Write your MySQL query statement below
select D.Name as Department, M.Name as Employee, M.Salary as Salary
from Department D, (select E1.Name, E1.Salary, E1.DepartmentId from Employee E1 join
                    (select Name, MAX(Salary) as Salary, DepartmentId from Employee group by DepartmentId)as E2
                     on E1.DepartmentId = E2.DepartmentId
                     where E1.Salary = E2.Salary)as M
where D.Id = M.DepartmentId;