Question 207810 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – filtering max value in each group, who can tell me where is my SQL statement wrong?

I want to filter out the record with the highest salary for each department. If there are multiple records that meet this condition in a department, keep all of them.Then I write SQL following:

SELECT id, name, salary, departmentId
  FROM Employee
  GROUP BY departmentId
  HAVING salary = MAX(salary)

But it’s not working correctly.

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

id is the primary key column for this table.

The result of the execution is:

| id | name  | salary | departmentId |
| -- | ----- | ------ | ------------ |
| 3  | Henry | 80000  | 2            |

But the result that I expected is like this:

| id | name  | salary | departmentId |
| -- | ----- | ------ | ------------ |
| 2  | Jim   | 90000  | 1            |
| -- | ----- | ------ | ------------ |
| 3  | Henry | 80000  | 2            |
| -- | ----- | ------ | ------------ |
| 5  | Max   | 90000  | 1            |

I want to know why I can’t get the result that I expect? what’s wrong with my SQL? I know how to write the correct SQL statement now. I just want to know what exactly is wrong with my SQL statement.

Tags:

2

Answers


  1. 0

    You can have this by using a subquery to find the max salary with respect to the department.

    SELECT 
  * 
FROM
  `Employee` e 
WHERE e.`salary` = 
  (SELECT 
    MAX(e1.`salary`) 
  FROM
    `Employee` e1 
  WHERE e1.`departmentId` = e.`departmentId`)

    OR

    SELECT e.id, e.name, e.salary, e.departmentId
FROM Employee e
INNER JOIN (
  SELECT departmentId, MAX(salary) AS max_salary
  FROM Employee
  GROUP BY departmentId
) subquery
ON e.departmentId = subquery.departmentId AND e.salary = subquery.max_salary;

    This answer also works for MySQL 5.6
    If you are using MySQL 8.X, you have your answer already in the comment section

    Login or Signup to reply.
  2. 0 
    select 
se.id, 
se.name, 
se.salary, 
se.department_id 
from stack_employees as se
join (
    select 
    department_id, 
    max(salary) as max_salary 
    from stack_employees
    group by department_id) di
on se.department_id = di.department_id
and se.salary = di.max_salary;

    this will give you the desired output.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search