CODEWITHSUNDEEP
mysqlsqlsubquery
DEVLIFEE7
DEVLIFEE7

Reputation: 17

MySQL Subquery - Max Amount

I am new to subqueries, and I am trying to understand why my regular query isn't returning the same result as this subquery. I am following this tutorial and it is the first "Try it out" question. ->

My query is: 

SELECT customerNumber,
       checkNumber,
       MAX(amount)
FROM payments

Upvotes: 0

Views: 92

Answers (2)

Freizar
Freizar

Reputation: 1

The query you are testing from the tutorial is different, it gets you the row with the maximun amount (just one row):

SELECT customerNumber,
       checkNumber,
       amount
FROM payments
WHERE amount = (
    SELECT MAX(amount) 
        FROM payments
);

And the query with the aggregation function gets the maximum amount for each combination of customerNumber and checkNumber (could be several combinations):

SELECT customerNumber,
           checkNumber,
           MAX(amount)
    FROM payments
    group by customerNumber,
           checkNumber

It all depends on what you want to get.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

As you used aggregate function max() so you are bound to use group by and your query will be like below

 SELECT customerNumber,
           checkNumber,
           MAX(amount)
    FROM payments
    group by customerNumber,
           checkNumber

Upvotes: 1

Related Questions