Department Highest Salary Problem

Description

LeetCode Problem 184.

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.

MySQL Solution

select
    Department.name as 'Department',
    Employee.name as 'Employee',
    Salary
from
    Employee
join
    Department on Employee.DepartmentId = Department.Id
where
    (Employee.DepartmentId , Salary) in
    (select
        DepartmentId, max(Salary)
    from
        Employee
    group by DepartmentId)

26 Dec 2020

Database

« Article Views II Problem Get Highest Answer Rate Question Problem »

LeetCode Database - Medium LeetCode 177 LeetCode 178 LeetCode 180 LeetCode 534 LeetCode 550 LeetCode 570 LeetCode 574 LeetCode 578 LeetCode 580 LeetCode 585 LeetCode 602 LeetCode 608 LeetCode 612 LeetCode 614 LeetCode 626 LeetCode 1045 LeetCode 1070 LeetCode 1077 LeetCode 1098 LeetCode 1107 LeetCode 1112 LeetCode 1126 More LeetCode Database MySQL Tutorials

Department Top Three Salaries Problem

LeetCode 185. Write a SQL query to find employees who...

Database MySQL Leetcode

27 Dec 2020