MAX function used with GROUP BY clause

Question

I have database with name, salary and department of employees. I need a query for getting employee(s) with highest salaries in each department.

Database:

create table test(
    employee_name VARCHAR(255),
    department VARCHAR(255),
    salary INT  
);

Data:

INSERT INTO test(employee_name, department, salary) VALUES 
("John", "DepartmentA", 1500),
("Sarah","DepartmentA", 1600),
("Romel","DepartmentA", 1400),
("Victoria","DepartmentB", 1400),
("Maria",   "DepartmentB", 1600);

My tries:

1.1 WHERE MAX(salary) = salary GROUP BY department

SELECT employee_name, salary FROM test WHERE MAX(salary) = salary GROUP BY department;
ERROR 1111 (HY000): Invalid use of group function

1.2. when I replace MAX(salary) with hardcoded value, it works as I expect:

SELECT employee_name, salary FROM test WHERE 1600 = salary GROUP BY department;
+---------------+--------+
| employee_name | salary |
+---------------+--------+
| Sarah         |   1600 |
| Maria         |   1600 |
+---------------+--------+
2 rows in set (0.00 sec)

2. Wrong answer with having clause (single result, not per department):

   SELECT employee_name, salary FROM test GROUP BY department HAVING MAX(salary) = salary;

   +---------------+--------+ | employee_name | salary | +---------------+--------+ | Maria | 1600 | +---------------+--------+ 1 row in set (0.00 sec)

What I expect as result:

Sarah, DepartmentA
Maria, DepartmentB

Answer 1

First you have to get the maximum salary for each department:

SELECT department, max(salary) as max_salary
FROM test
GROUP BY department

then you can join back this subquery to the test table:

SELECT t.*
FROM
  test t INNER JOIN (
    SELECT department, max(salary) as max_salary
    FROM test
    GROUP BY department
  ) d ON t.department=d.department AND t.salary=d.max_salary

Answer 2

You can write a correlated subquery as:

select employee_name,
department,
salary 
from test T1 where T1.salary = (select max(T2.salary)
                                from test T2
                                where T1.department = T2.department
                                group by T2.department
                                )

demo

... but note that it will be much slower than the uncorrelated equivalent

Answer 3

Try this:

SELECT t1.employee_name, t1.department
FROM test t1 
INNER JOIN (SELECT t2.department, MAX(t2.salary) AS salary
            FROM test t2
            GROUP BY t2.department
          ) AS t2 ON t1.department = t2.department AND t1.salary = t2.salary;