184_Department Highest Salary

[medium]

TheEmployeetable holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

TheDepartmenttable holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Solution 1

SELECT 
    D.Name AS Department, 
    E.Name AS Employee, 
    E.Salary 
FROM
    Employee AS E,
    (SELECT DepartmentId, MAX(Salary) AS max FROM Employee GROUP BY DepartmentId) AS T,
    Department AS D
WHERE E.DepartmentId = T.DepartmentId 
  AND E.Salary = T.max
  AND E.DepartmentId = D.id

Solution 2

SELECT
    Department.name AS Department,
    Employee.name AS Employee,
    Salary
FROM
    Employee
    JOIN Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;

Solution 3

SELECT 
    D.Name AS Department, 
    E1.Name AS Employee, 
    E1.Salary AS Salary
FROM 
Department AS D
    JOIN Employee AS E1 ON D.Id = E1.DepartmentId
WHERE E1.Salary =  (
                    SELECT MAX(E2.Salary)
                    FROM Employee AS E2
                    WHERE E2.DepartmentId = E1.DepartmentId
                    );

Solution 4

SELECT Department.Name Department, e1.Name Employee, e1.Salary
FROM employee e1 
    LEFT JOIN employee e2 ON e1.DepartmentId = e2.DepartmentId AND e1.Salary < e2.Salary
    JOIN Department ON e1.DepartmentId = Department.Id
WHERE e2.Salary is null