MySQL MAX() Function – Get the Maximum Value

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:



The following is a demo order table that we will use in our examples.

#ORD_000012019-10-03Annie Mitchell2000.00
#ORD_000022019-10-03Grant Daniels1890.40
#ORD_000032019-10-04Lindsay Leonard1768.00
#ORD_000042019-10-04Mario Simmons2340.50
#ORD_000052019-11-10Thomas Pratt1370.50
#ORD_000062019-11-12Ronald Houston2780.50
#ORD_000072019-11-12Vera Bell1290.00
#ORD_000082019-11-12Gloria Harvey2450.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;


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';


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';


MySQL returns 2340.50, the largest amount between 2019-10-01 and 2019-10-05.


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.

See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment