Write an SQL query to find employees who have the
highest salary in eah of the departments. Return the result table in any order.
table
name: Employee
table name: Department
Solution:
with cte as
(
select rank() over (partition by departmentId order by e.salary desc) as rnk,Salary,
d.name as department,e.name as employee
from Employee as e
join Department as d on e.departmentId = d.id
)
select department,employee, Salary from cte where rnk =1
Output:
SQL Script:
Create table
Employee (id int, name varchar(255), salary int, departmentId int)
Create table
Department (id int, name varchar(255))
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')
insert into Department (id, name) values ('1', 'IT')
insert into Department (id, name) values ('2', 'Sales')