Problem Description
Find employees who have the highest salary in each of the departments. A department may have multiple employees sharing the highest salary; all of them should be included in the result. ### Example **Employee Table:** | id | name | salary | departmentId | | :--- | :--- | :--- | :--- | | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | **Department Table:** | id | name | | :--- | :--- | | 1 | IT | | 2 | Sales | **Expected Output:** | Department | Employee | Salary | | :--- | :--- | :--- | | IT | Jim | 90000 | | IT | Max | 90000 | | Sales | Henry | 80000 |
Database Schema
undefined
Sample SQL Solution
-- Write your SQL query to find employees with highest salary in each department
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
);