department-highest-salary
Write an SQL query to find employees who have the highest salary in each of the departments.
Return the result table inย any order.
The query result format is in the following example.
Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
SQL
๋ณต์ฌ
ํ์ด 1.
SELECT d.name AS department
, e.name AS employee
, e.salary
FROM employee AS e
INNER JOIN (
-- ๋ถ์์์ ๊ฐ์ฅ ๋ง์ด ๋ฒ ๋์ ๊ทธ ์๊ธ๊ณผ ๋ถ์ id
SELECT departmentid, MAX(salary) AS highest_salary
FROM employee
GROUP BY departmentid
) AS dh
ON e.departmentid = dh.departmentid
AND e.salary = dh.highest_salary
INNER JOIN department AS d ON e.departmentid = d.id
SQL
๋ณต์ฌ
ํ์ด 2. ์๋์ฐ function
# Write your MySQL query statement below
SELECT T.Department, T.Employee, T.Salary
FROM(
SELECT D.name AS Department
,E.name AS Employee
,E.salary AS e_salary
,MAX(salary) OVER (PARTITION BY departmentId) As Salary
FROM Employee E
JOIN Department D ON E.departmentID = D.id
) T
WHERE T.Salary = T.e_salary
;
SQL
๋ณต์ฌ