How do I use MAX() to return the row that has the max value?

Question

I have table orders with fields id, customer_id and amt:

SQL Fiddle

And I want get customer_id with the largest amt and value of this amt.

I made the query:

SELECT customer_id, MAX(amt) FROM orders;

But the result of this query contained an incorrect value of customer_id.

Then I built such the query:

SELECT customer_id, MAX(amt) AS maximum FROM orders GROUP BY customer_id ORDER BY maximum DESC LIMIT 1;

and got the correct result.

But I do not understand why my first query not worked properly. What am I doing wrong?

And is it possible to change my second query to obtain the necessary information to me in a simpler and competent way?

Answer 1

MySQL will allow you to leave GROUP BY off of a query, thus returning the MAX(amt) in the entire table with an arbitrary customer_id. Most other RDBMS require the GROUP BY clause when using an aggregate.

I don't see anything wrong with your 2nd query -- there are other ways to do it, but yours will work fine.