How to Use the MySQL MAX() Function and Examples
In this tutorial, you will learn how to use the MySQL MAX() function. MAX() is an aggregate function that returns the maximum value in a group of values.
Other aggregate functions in MySQL are SUM() function, COUNT() function, MIN() function, etc.
For example, suppose you have a list of items for sale, and you want to know which item has the highest selling price. In this case, you can use the MAX() function.
MySQL MAX() Syntax
The syntax of the MAX() function is as follows:
MAX(expression)
Examples
The following is a demo order table that we will use in our examples.
| order_number | order_date | customer | amount |
|---|---|---|---|
| #ORD_00001 | 2019-10-03 | Annie Mitchell | 2000.00 |
| #ORD_00002 | 2019-10-03 | Grant Daniels | 1890.40 |
| #ORD_00003 | 2019-10-04 | Lindsay Leonard | 1768.00 |
| #ORD_00004 | 2019-10-04 | Mario Simmons | 2340.50 |
| #ORD_00005 | 2019-11-10 | Thomas Pratt | 1370.50 |
| #ORD_00006 | 2019-11-12 | Ronald Houston | 2780.50 |
| #ORD_00007 | 2019-11-12 | Vera Bell | 1290.00 |
| #ORD_00008 | 2019-11-12 | Gloria Harvey | 2450.50 |
Example 1: MAX() without WHERE clause
For example, if you want to get the maximum amount in the "Amount" column, you can write the SQL query as follows:
SELECT MAX(orders.amount) AS LargestAmount
FROM orders;
Result:
The above statement returns 2780.50, which is the largest value.
Example 2: MAX() and WHERE clause
The following SQL statement returns the maximum amount where order_date is 2019-11-12:
SELECT MAX(orders.amount) AS LargestAmount
FROM orders
WHERE order_date = '2019-11-12';
Result:
The above SQL query will return 2780.50.
Example 3: MAX() and AND operator
This example shows how we use the MAX() function and the AND operator in the WHERE clause.
The following SQL query returns the maximum amount between two dates:
SELECT MAX(o.amount) AS LargestAmount
FROM orders o
WHERE o.order_date >= '2019-10-01' AND o.order_date <= '2019-10-05';
Result:
MySQL returns 2340.50, the largest amount between 2019-10-01 and 2019-10-05.
Summary
In this tutorial, you have learned how to use the MySQL MAX() function. MAX() is an aggregate function that finds the maximum value among a group of values. If you want to get the minimum value in a group of values, you can use the MySQL MIN() function.