Leet Code | MySQL | 184. Department Highest Salary

184. Department Highest Salary

 

https://leetcode.com/problems/department-highest-salary/

Department Highest Salary - LeetCode

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

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).

 

# Write your MySQL query statement below
SELECT d.name AS department
    , e.name AS employee
    , e.salary
FROM employee AS e
    INNER JOIN (
        -- 부서에서 가장 많이 벌 때에 그 임금과 부서id
        SELECT departmentid, MAX(salary) AS max_salary
        FROM employee
        GROUP BY departmentid
        ) AS dh ON e.departmentid = dh.departmentid
                AND e.salary = dh.max_salary
    INNER JOIN department AS d ON d.id = e.departmentid

/* MAX(), RANK(), DENSE_RANK() */
SELECT ms.deparment
	, ms.name AS Employee
	, ms.salary
FROM(
    SELECT employee.name
        , employee.salary
        , department.name
        , MAX(salary) OVER (PARTITION BY departmentId) max_salary
    FROM employee
        INNER JOIN department ON employee.departmentid = department.Id
    ) ms
WHERE ms.salary = ms.max_salary