CODEWITHSUNDEEP
mysqlsqlmax
sai
sai

Reputation: 323

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

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

Upvotes: 3

Views: 8321

Answers (3)

Harsh Awachar
Harsh Awachar

Reputation: 1

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;

Upvotes: -1

peterm
peterm

Reputation: 92815

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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;

Upvotes: 3

Related Questions