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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://lei-d.gitbook.io/sql/leetcode/184department-highest-salary.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.