Grouping by aggregate function max in MySQL

Question

I have the table instructor as follows:

+-------+------------+------------+----------+
| ID    | name       | dept_name  | salary   |
+-------+------------+------------+----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu         | Finance    | 90000.00 |
| 15151 | Mozart     | Music      | 40000.00 |
| 22222 | Einstein   | Physics    | 95000.00 |
| 32343 | El Said    | History    | 60000.00 |
| 33456 | Gold       | Physics    | 87000.00 |
| 45565 | Katz       | Comp. Sci. | 75000.00 |
| 58583 | Califieri  | History    | 62000.00 |
| 76543 | Singh      | Finance    | 80000.00 |
| 76766 | Crick      | Biology    | 72000.00 |
| 83821 | Brandt     | Comp. Sci. | 92000.00 |
| 98345 | Kim        | Elec. Eng. | 80000.00 |
+-------+------------+------------+----------+

The query

 select dept_name, max(salary) from instructor group by dept_name;

will give me the max salary for each department in the instructor.

However, I want to get all the columns in the result, that is also ID and name of the highest paid instructor in each department. But I am not sure how to do that.

This type of question has been answered many times here in SO. If you search for *greatest per group mysql* you will get plenty of examples. — Giorgos Betsos, Aug 28 '18 at 06:12
@GiorgosBetsos [This is the usual duplicate](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) folks are marking with. But, the accepted answer is terrible IMO, and you really have to scroll down a while to find something which looks good. — Tim Biegeleisen, Aug 28 '18 at 06:13

score 1 · Accepted Answer · edited Aug 28 '18 at 06:43

1

You can use your current query as a subquery which filters the original table:

SELECT i1.*
FROM instructor i1
INNER JOIN
(
    SELECT dept_name, MAX(salary) AS salary
    FROM instructor
    GROUP BY dept_name
) i2
    ON i1.dept_name = i2.dept_name AND i1.salary = i2.salary;

edited Aug 28 '18 at 06:43

Strawberry

33,750
13
40
57

answered Aug 28 '18 at 06:12

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 2 · answered Aug 28 '18 at 06:15

0

Please use below query:

SELECT ID, name, dept_name, salary
FROM( select ID, name, dept_name, salary, ROW_NUMBER() OVER(PARTITION BY  dept_name ORDER BY salary DESC) as ranks
 from instructor)T
 WHERE ranks = 1

answered Aug 28 '18 at 06:15

JERRY

1,165
1
8
22

Note: This query would only run on MySQL versions 8+ or later. – Tim Biegeleisen Aug 28 '18 at 06:41

Zaynul Abadin Tuhin · Answer 3 · 2018-08-28T06:32:59.857

0

you can also use correlated sub-query

select t.* from instructor  t
 where salary in
  ( 
    select  max(salary) from instructor t1 
    where t1.dept_name=t.dept_name
    group by t1.dept_name 
   )

http://sqlfiddle.com/#!9/f7018c/1

edited Aug 28 '18 at 06:32

answered Aug 28 '18 at 06:24

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 4 · answered Aug 28 '18 at 06:57

0

SELECT ins.id , ins.name 
FROM instructor ins 
inner join (
    SELECT ins2.id as id , MAX(ins2.salary) as salary 
    FROM instructor ins2 
    group by ins2.id
) as insG 
ON ins.id = insG.id AND ins.salary = insG.salary

answered Aug 28 '18 at 06:57

Morteza Jalambadani

2,190
6
21
35

score 0 · Answer 5 · answered Aug 28 '18 at 09:33

0

You can use correlated subquery :

select i.*
from instructor i
where salary = (select max(i1.salary)
                from instructor i1
                where i1.dept_name = i.dept_name
               );

answered Aug 28 '18 at 09:33

Yogesh Sharma

49,870
5
26
52

Grouping by aggregate function max in MySQL

5 Answers5