Question

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  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | 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, your SQL query should return the following rows (order of rows does not matter).

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Explanation:

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

SQL Schema

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int)
Create table If Not Exists Department (Id int, Name varchar(255))
Truncate table Employee
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Jim', '90000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Henry', '80000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Sam', '60000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Max', '90000', '1')
Truncate table Department
insert into Department (Id, Name) values ('1', 'IT')
insert into Department (Id, Name) values ('2', 'Sales')

My Interesting Code

select temp1.Department, temp1.Employee, temp1.Salary
from (
    select Employee.Name as Employee, Employee.Salary, Employee.DepartmentId, Department.Name as Department
    from Employee
    inner join Department on Employee.DepartmentId = Department.Id
) temp1,
    (select max(Salary) as Salary, DepartmentId
    from Employee
    group by DepartmentId) temp2
where temp1.DepartmentId = temp2.DepartmentId and temp1.Salary = temp2.Salary

My Perspective

This question is a little bit complex, but I think there would be other better solutions. Firstly, you can find all the highest salary of different departments. Then, you can just use this table - “temp2”- to find relevant people. Because different people meay have the same salary, you need to do the second step, finding all the relevant people.

However, when I submitted for the first time, I failed. Becasue according to the question, if an employee does not belong to any department, the result would be null, even if the employee’s salary is extremely high. Therefore, the “join” should use “inner join” rather than “left join”. “Inner join” would only return the compared successfully ones, others would be set null.

Also, there is a useful link, and I think it can help you. (chinese version)