Department Highest Salary

You can find the employees who have the highest salary in each department by using the following SQL query:

1
2
3
4
5
6
7
8
9

SELECT d.name AS Department,
       e.name AS Employee,
       e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE (e.departmentId, e.salary) IN
    (SELECT departmentId, MAX(salary)
     FROM Employee
     GROUP BY departmentId);

Here’s a breakdown of how the query works:

  1. Inner Query: (SELECT departmentId, MAX(salary) FROM Employee GROUP BY departmentId) is used to find the highest salary for each department.

  2. Main Query: It joins the Employee and Department tables on the departmentId column and filters the rows where the combination of departmentId and salary matches the result of the inner query. This ensures that only employees with the highest salary in their respective departments are selected.

  3. SELECT Clause: Finally, the columns are selected for the output: the department name, employee name, and salary.

The result of this query will match the expected format and contain the employees with the highest salary in each department.