Q184DepartmentHighestSalary.sql

/*
184. Department Highest Salary

Description:
Write an SQL query to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
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 |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+
Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
*/

select d.name as Department, e.name as Employee, e.salary as Salary 
from Employee e inner join Department d on d.id=e.departmentId 
where (e.departmentId,e.salary) IN (select departmentId, max(salary) 
from Employee group by departmentId);

/*
Find all employee and department details from respective tables joined by "INNER JOIN"
with the condition that departmentId and salary matches/is in the nested query 
which return all departmentId with respective maximum salary 
*/