SQL max() function returns wrong value for row with maximum value

Question

THIS IS MY TABLE STRUCTURE:

Anees   1000.00
    Rick    1200.00
    John    1100.00
    Stephen 1300.00
    Maria   1400.00

I am trying to find the MAX(salary) and the persons name .

this is the query I use

Select MAX(salary),emp_name FROM emp1

I get 1400.00 and Anees.

While the 1400 is correct the Anees is wrong,it should be maria. What changes do I need to make

This is a common problem/issue with MySQL because it does not *require* the use of all aggregate columns or columns specified in a group-by: this is an *incorrect mixed-aggregate query*. In this case MySQL can pick *any* value for that column and return it along with the aggregate value. — user2246674, Jul 23 '13 at 23:04
See http://stackoverflow.com/questions/2081211/mysql-select-maxscore-doesnt-return-the-relevant-row-data-how-to-solve-the/2081287 for a duplicate question, better explanation, and solutions to this query. Alternative solutions can be seen http://stackoverflow.com/questions/7604893/sql-select-row-from-table-where-id-maxid?lq=1 and http://stackoverflow.com/questions/537223/mysql-control-which-row-is-returned-by-a-group-by?lq=1 — user2246674, Jul 23 '13 at 23:06

score 3 · Answer 1 · answered Jul 23 '13 at 23:06

3

MySQL allows you to have columns in the select statement that are not in aggregate functions and are not in the group by clause. Arbitrary values are returned.

The easiest way to do what you want is:

select t.*
from t
order by salary desc
limit 1;

answered Jul 23 '13 at 23:06

Gordon Linoff

1,242,037
58
646
786

what happen when 2 employees have equal maximum salary – Praveen Prasannan Jul 24 '13 at 04:32

peterm · Accepted Answer · 2013-07-24T05:38:09.377

1

Gordon gave an explanation why and the simplest way to get want you want. But if you for some reason want to use MAX() you can do it like this

SELECT emp_name, salary
  FROM emp1 
 WHERE salary = 
(
  SELECT MAX(salary) salary
    FROM emp1
)

Output:

| EMP_NAME | SALARY |
---------------------
|    Maria |   1400 |

Here is SQLFiddle demo

edited Jul 24 '13 at 05:38

answered Jul 23 '13 at 23:14

peterm

91,357
15
148
157

score -1 · Answer 3 · answered Mar 29 '22 at 14:03

It's giving the wrong output because of the datatype you mentioned. If you mentioned the salary datatype number or integer, it will give you the correct answer.

It will give you the correct answer no problem make sure your datatype is integer or number.

select max(salary) from emp;

SQL max() function returns wrong value for row with maximum value

3 Answers3