How to use MAX () to return a row with maximum value?

Question

I have a table orderswith fields id, customer_idand amt:

SQL Fiddle

And I want to get customer_idwith the greatest amt and value of this amt.

I made a request:

SELECT customer_id, MAX(amt) FROM orders;

But the result of this query contained the wrong value customer_id.

Then I built a query like this:

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 request did not work properly . What am I doing wrong?

And is it possible to change my second request in order to get the necessary information for me in a simpler and more competent way?

Answer 1

MySQL GROUP BY, MAX(amt) customer_id. GROUP BY .

- , .

Answer 2

SQL , MAX SUM, , , GROUP BY.

customer_id, max amt. , SQL , . :

select customer_id from orders where amt = ( select max(amt) from orders);

, , .

, , , , MAX SUM. , MAX , . SUM , .

Answer 3

, GROUP BY-ed . , MySQL .

http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html

MySQL GROUP BY, , GROUP BY. , , . , ORDER BY. , , ORDER BY .

Answer 4

MAX() , , . . MySQL , GROUP ( GROUP sinse, ), .

, :

SELECT customer_id, amt FROM orders ORDER BY amt DESC LIMIT 1

customer_id amt, , .