Скачать или смотреть MySQL MAX Function

In this tutorial, you will learn how to use the MySQL MAX() function to get the maximum value in a set of values.
MySQL MAX() function examples:
We’ll use the payments table in the sample database to demonstrate the MAX() function.

This example uses the MAX() function to return the largest amount of all payments:

SELECT MAX(amount)
FROM payments;
In this example, the MAX() function checks all values in the amount column of the payments table to find the largest amount.

Using MySQL MAX() function with WHERE clause
The following statement uses the MAX() function to find the largest payment in 2004:

SELECT
MAX(amount) largest_payment_2004
FROM
payments
WHERE
YEAR(paymentDate) = 2004;
First, use a condition in the WHERE clause to get only payments whose year is 2004. We used the YEAR() function to extract the year from the payment date.
Then, use the MAX() function in the SELECT clause to find the largest amount of payments in 2004.

Using MAX() function in subquery example
To obtain not only the largest payment amount but also additional payment information, such as customer number, check number, and payment date, you can utilize the MAX() function within a subquery, as demonstrated in the following query:

SELECT * FROM payments WHERE amount = (SELECT MAX(amount)
FROM payments);
The subquery returns the largest amount of all payments.
The outer query gets the payment whose amount is equal to the largest amount returned from the subquery and also other payment information.

Another way to do this without using the MAX() function is to sort the result set in descending order using the ORDER BY clause and get the first row in the result set using the LIMIT clause as follows:

SELECT * FROM payments ORDER BY amount DESC LIMIT 1;

Using MySQL MAX() with GROUP BY clause example
To find the maximum value for every group, you use the MAX function with the GROUP BY clause.

This statement uses the MAX() to get the largest payment from each customer:

SELECT customerNumber, MAX(amount)
FROM payments GROUP BY customerNumber ORDER BY MAX(amount);
First, the GROUP BY clause group payments into groups by customer number.
Second, the MAX() function returns the largest payment in each group.

finds the largest payment of each customer; and based on the returned payments, gets only payments whose amounts are greater than 80,000 .

SELECT
customerNumber, MAX(amount)
FROM
payments
GROUP BY customerNumber
HAVING MAX(amount) greater than 80000
ORDER BY MAX(amount);