SQL Return row from MAX() function-CodePudding

My first foray into SQL and I'm having some difficulty applying the MAX() function.

If I run the following, I receive the correct returned value:

SELECT MAX(count) 
FROM readings

However, when I try to also return fields related to that value, I get an incorrect return. Running the following returns the correct 'count' value but an incorrect 'location' value

SELECT MAX(count), location 
FROM readings

What I expected from the above, are results the same as from:

SELECT count, location 
FROM readings
ORDER BY count DESC 
LIMIT 1

Could you please advise if it is possible to achieve this using the MAX() function or if I have just misunderstood what MAX actually does!

Your advice is greatly appreciated.

CodePudding user response：

What database system are you using? MAX is an aggregation function that should be operating across the entire table, while selecting a single value (like location in your query) is operating only on a single row. In most databases, if you want to select another column, you must specify that column in a GROUP BY clause or also wrap it in a similar aggregation function.

To get the value of location in the same row, you typically should use a subselect, like this:

SELECT count, location
FROM readings
WHERE count = (SELECT MAX(count) FROM readings);

Note that this doesn't guarantee a single result, though; there could be several rows that match the maximum count value!