Unable to identify syntax issue with using MAX and OVER PARTITION BY

I’m trying to answer a SQL Leetcode question on Medium Difficulty. The question is asking me to find the people that have the highest salary per department.

I tried using GROUP BY but wasn’t able to figure out that approach so I tried to use the PARTITION BY in order to find the max salary for each department. Afterwards, I would add a final WHERE statement where I could filter down to people with a salary that equals the max salary.

This is the query I have so far:

SELECT 
    Department.Name AS Department, 
    Employee.Name AS Employee, 
    Employee.Salary,
    MAX(Employee.Salary) OVER (PARTITION BY Department.Name) AS MaxSalary
FROM Employee
LEFT JOIN Department ON Department.id = Employee.DepartmentId
;

There’s two tables.

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

and the correct output should look like this.

CORRECT EXPECTED OUTPUT:

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Running my current query leads to a runtime error with this message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY Department.Name) AS MaxSalary
FROM Employee
LEFT JOIN Department O' at line 7

I’ve looked over the syntax multiple times so I assume it’s a incorrect understanding of how PARTITION BY works. I was expecting that I would see the max salary for each department listed on the right for each individual. Something like this:

+----+-------+--------+--------------+-----------+
| Id | Name  | Salary | DepartmentId | MaxSalary |
+----+-------+--------+--------------+-----------+
| 1  | Joe   | 70000  | 1            | 90000     |
| 2  | Jim   | 90000  | 1            | 90000     |
| 3  | Henry | 80000  | 2            | 80000     |
| 4  | Sam   | 60000  | 2            | 80000     |
| 5  | Max   | 90000  | 1            | 90000     |
+----+-------+--------+--------------+ -----------+

After achieving that, I was going to add this line:

WHERE Employee.Salary = MaxSalary;

What is the proper way to do this?

Answer

You can consider using one of the following:

SELECT D.Name AS Department, E.Name AS Employee, E.Salary
FROM Employee AS E LEFT JOIN Department AS D ON E.DepartmentId = D.id
WHERE (E.Salary = (SELECT MAX(X.Salary) FROM Employee AS X WHERE (X.DepartmentId = E.DepartmentId)));

SELECT D.Name AS Department, E.Name AS Employee, E.Salary
FROM Employee AS E LEFT JOIN Department AS D ON E.DepartmentId = D.id
INNER JOIN (SELECT DepartmentId, MAX(Salary) AS MaxSalary FROM Employee GROUP BY DepartmentId) AS X ON E.DepartmentId = X.DepartmentId AND E.Salary = X.MaxSalary;

Advertisement

Answer