MySQL - Retrieving the record with the maximum value

Question

I've a table like below on my MySQL database:

employee
-------------------------
id    name    num
1     Jack    1
2     John    1
3     Jack    2
4     Steve   1
5     Jack    3
6     Steve   2
7     Carol   1

The thing I want to do is to retrieve names with the highest "num" value.

For example:

5     Jack    3

There more than one records named Jack but I want to get the one with the maximum "num" value.

I've tried to use the query below:

SELECT MAX(num),id,name FROM `employee`

But it returns the id and name of first record (first "Jack") but the num of the latest.

MAX(num)    id     num
-----------------------
3           1      Jack

Of course I can try to use "Order by" but I also want to retrieve the only record with the highest value.

SELECT num,id,name FROM `employee` order by num desc

This query will also return all the records including the smaller ones.

id    name    num
-------------------------
3     5       Jack
2     3       Jack
2     6       Steve
1     1       Jack
1     2       John
1     4       Steve
1     7       Carol

When I try to group by name, it returns the records grouped by name but again only the small records.

The both of the queries below give the same result:

SELECT num,id,name FROM `employee` group by name order by num desc 


SELECT * FROM
(
select * from `employee` order by num desc
) as employee_tmp
group by name
order by num desc

Again, it groups with only the first and smallest records.

id  name    num
1   Jack    1
2   John    1
4   Steve   1
7   Carol   1

Carsten Massmann · Accepted Answer · 2017-06-04T10:57:34.480

1

Simply add a LIMIT 1 clause to your query like

SELECT num,id,name FROM `employee` order by num desc LIMIT 1

Edit:
OK, I misunderstood your original question. The following lists each name with the highest num value:

SELECT a.* FROM employee a 
INNER JOIN (
  SELECT name na, MAX(num) nu FROM employee 
  GROUP BY name ) b ON na=name AND nu=num

See here for a demo: http://rextester.com/MOL28087

edited Jun 04 '17 at 10:57

answered Jun 04 '17 at 10:41

Carsten Massmann

26,510
2
22
43

what about the other records? I want all of them with the highest values. Like "Jack with num 3", "Steve with num 2" – amone Jun 04 '17 at 10:45
@amone, please check again, I updated my answer. – Carsten Massmann Jun 04 '17 at 10:59

score 0 · Answer 2 · answered Jun 04 '17 at 10:47

0

You need to use inner query with MAX, e.g.:

SELECT * 
FROM employee
WHERE num = (
    SELECT MAX(num) FROM employee
);

This would give you the required result.

answered Jun 04 '17 at 10:47

Darshan Mehta

30,102
11
68
102

It returns the only one record. I want all of them with the highest values. Like "Jack with num 3", "Steve with num 2" – amone Jun 04 '17 at 10:50
Highest value here is `3`. So, it will return all the records with value `3`. Please note that `2` is not the highest value so corresponding records will not be returned. – Darshan Mehta Jun 04 '17 at 10:51

score 0 · Answer 3 · answered Jun 04 '17 at 10:53

MAX(column_name) will return max of all records,

SELECT emp.* FROM `employee` emp INNER JOIN
(SELECT name,MAX(num) as num FROM `employee` GROUP BY name) tbl
ON emp.`name` = tbl.`name` AND emp.`num` = tbl.`num`

We have used inner query to find out the name and max num of that name and then joined with the main table on name col and num col. We need to mention group by so that it gives the name and max of each num for that name

I think, this should solve your purpose

MySQL - Retrieving the record with the maximum value

3 Answers3