2

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
  • 7,373
  • 23
  • 27
sai
  • 183
  • 1
  • 6
  • 18
  • 2
    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

2 Answers2

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;
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
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

peterm
  • 85,273
  • 13
  • 129
  • 142