> For the complete documentation index, see [llms.txt](https://lei-d.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://lei-d.gitbook.io/sql/leetcode/184department-highest-salary.md).

# 184\_Department Highest Salary

\[medium]

The`Employee`table 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            |
+----+-------+--------+--------------+
```

The`Department`table 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

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

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

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

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