Leetcode: Department Highest Salary

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 |
+————+———-+——–+

Answer:

Note that there can be more than one person who gets the highest salary. So If we write like this, we get just one of them. The result is wrong.

select D.Name, E.Name, max(E.Salary) 
from Employee as E, Department as D 
where D.Id = E.DepartmentId 
group by E.DepartmentId

I need to get the highest value first, then according to the value I get the ones whose salary is the value. So I use “IN” to help judge whether the pair of (Salary, DepartmentId) is the highest.

select D.Name, E.Name, E.Salary 
from Employee as E, Department as D 
where D.Id = E.DepartmentId 
and (E.Salary,E.DepartmentId) in 
(select MAX(Salary),DepartmentId from Employee group by DepartmentId)

The query from Table Department and from Subquery can be combined. I build a new table with columns (MAX_Salary, DepartmentId, DepartmentName). It will be quick.

select D.Name, E.Name, E.Salary 
from Employee as E, 
(
select MAX(E1.Salary) as M_S, E1.DepartmentId, D1.Name 
from Employee E1, Department D1
where E1.DepartmentId = D1.Id 
group by E1.DepartmentId
) as D 
where E.salary = M_S and E.DepartmentId = D.DepartmentId

Adding “ORDER BY” in subTable will make query more quickly.

select D.Name, E.Name, E.Salary 
from Employee as E, 
(
select MAX(E1.Salary) as M_S, E1.DepartmentId, D1.Name 
from Employee E1, Department D1
where E1.DepartmentId = D1.Id 
group by E1.DepartmentId
order by M_S
) as D 
where E.salary = M_S and E.DepartmentId = D.DepartmentId
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值