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

• user2246674: 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: See stackoverflow.com/questions/2081211/… for a duplicate question, better explanation, and solutions to this query. Alternative solutions can be seen stackoverflow.com/questions/7604893/… and stackoverflow.com/questions/537223/…

Answer 1

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;

• Praveen Prasannan: what happen when 2 employees have equal maximum salary

Answer 2

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

Answer 3

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;