MySQL MAX
Summary: in this tutorial, you will learn how to use the MySQL MAX()
function to get the maximum value in a set of values.
Introduction to MySQL MAX()
function
The MySQL MAX()
the function returns the maximum value in a set of values. The MAX()
function comes in handy in many cases such as finding the greatest number, the most expensive product, and the largest payment from customers.
Here is the basic syntax of the MAX()
function :
MAX(DISTINCT expression)
If you add the DISTINCT
operator, the MAX()
the function returns the maximum value of distinct values, which is the same as the maximum value of all values. It means that DISTINCT
does not take any effects in the MAX()
function.
Notice that DISTINCT
has effects in other aggregate functions such as COUNT()
, SUM()
, and AVG()
.
MySQL MAX()
function examples
We’ll use the payments
the table in the sample database to demonstrate the MAX()
function.
A) Using MySQL MAX()
function to find the maximum value in a column example
This example uses the MAX()
function to return the largest amount of all payments:
SELECT MAX(amount)
FROM payments;
In this example, the MAX()
the function checks all values in the amount column of the payments table to find the biggest amount.
B) Using MySQL MAX()
function with WHERE
clause
This statement uses the MAX()
function to find the largest payment in 2004:
SELECT
MAX(amount) largest_payment_2004
FROM
payments
WHERE
YEAR(paymentDate) = 2004;
In this example:
- First, use a condition in the
WHERE
clause to get only payments whose year is 2004. We used theYEAR()
function to extract the year from the payment date. - Then, use the
MAX()
function in theSELECT
clause to find the largest amount of payments in 2004.
This picture shows the output:
C) Using MySQL MAX()
function in subquery example
To get not only the largest payment amount but also other payment information such as customer number, check number, and payment date, you use the MAX()
function in a subquery as shown in the following query:
SELECT
*
FROM
payments
WHERE
amount = (SELECT
MAX(amount)
FROM
payments);
How it works.
- 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()
the 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;
If you don’t have an index on the amount
column, the second query with the LIMIT
a clause is faster because it examines all rows in the payments
table, while the first query examines all the rows in the payments
table twice, first once in the subquery and another in the outer query.
However, if the amount
the column is indexed, the first query executes faster.
D) 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 of each customer:
SELECT
customerNumber, MAX(amount)
FROM
payments
GROUP BY customerNumber
ORDER BY MAX(amount);
In this example:
- First, the
GROUP BY
clause group payments into groups by customer number. - Second, the
MAX()
the function returns the largest payment in each group.
E) Using MySQL MAX()
with HAVING
clause
When you use the MAX()
function with the GROUP BY
clause, you can find the maximum value for each group.
If you want to filter groups based on a condition, you can use the MAX()
function in a HAVING
clause.
The following query finds the largest payment of each customer; and based on the returned payments, get only payments whose amounts are greater than 80,000
.
SELECT
customerNumber, MAX(amount)
FROM
payments
GROUP BY customerNumber
HAVING MAX(amount) > 80000
ORDER BY MAX(amount);
If you want to see the names of customers instead of numbers, you can join the payments
table with the customers
table:
SELECT
customerName,
MAX(amount)
FROM
payments
INNER JOIN customers USING (customerNumber)
GROUP BY
customerNumber
HAVING
MAX(amount) > 80000
ORDER BY
MAX(amount);
Here is the output:
In this tutorial, you have learned how to use the MySQL MAX()
function to find the maximum value in a set of values.
0 Comments
CAN FEEDBACK
Emoji