The MySQL MAX() function is an aggregate function that returns the maximum value from an expression.

Typically, the expression would be a range of values returned as separate rows in a column, and you can use this function to find the maximum value from the returned rows. If there are no matching rows, MAX() returns NULL.

For example, you can use this function to find out which city has the largest population out of a list of cities.

Syntax

The syntax of MAX() goes like this:

MAX([DISTINCT] expr) [over_clause]

Where expr is the expression for which you want the maximum value.

The over_clause is an optional clause that works with window functions. Note that the over_clause can only be used if you don’t use the DISTINCT keyword.

The (optional) DISTINCT keyword can be used to eliminate duplicate values.

Basic Example

First, here’s the raw data that we’ll use in this example:

USE world;
SELECT Name, Population
FROM City
WHERE CountryCode = 'THA';

Result:

+-------------------+------------+
| Name              | Population |
+-------------------+------------+
| Bangkok           |    6320174 |
| Nonthaburi        |     292100 |
| Nakhon Ratchasima |     181400 |
| Chiang Mai        |     171100 |
| Udon Thani        |     158100 |
| Hat Yai           |     148632 |
| Khon Kaen         |     126500 |
| Pak Kret          |     126055 |
| Nakhon Sawan      |     123800 |
| Ubon Ratchathani  |     116300 |
| Songkhla          |      94900 |
| Nakhon Pathom     |      94100 |
+-------------------+------------+

We can use the MAX() function to find the city with the largest population (i.e. the row with the maximum value in its population column).

USE world;
SELECT MAX(Population) AS 'Maximum Value'
FROM City
WHERE CountryCode = 'THA';

Result:

+---------------+
| Maximum Value |
+---------------+
|       6320174 |
+---------------+

The GROUP BY Clause

We can use the GROUP BY clause to list out each country, along with the population of that country’s largest city (by population):

USE world;
SELECT District, MAX(Population) AS 'Max Value'
FROM City
WHERE CountryCode = 'AUS'
GROUP BY District;

Result:

+-----------------+-----------+
| District        | Max Value |
+-----------------+-----------+
| New South Wales |   3276207 |
| Victoria        |   2865329 |
| Queensland      |   1291117 |
| West Australia  |   1096829 |
| South Australia |    978100 |
| Capital Region  |    322723 |
| Tasmania        |    126118 |
+-----------------+-----------+

The ORDER BY Clause

We can also use the ORDER BY clause to specify a column with which to order by:

USE world;
SELECT District, MAX(Population) AS 'Max Value'
FROM City
WHERE CountryCode = 'AUS'
GROUP BY District
ORDER BY `Max Value` ASC;

Result:

+-----------------+-----------+
| District        | Max Value |
+-----------------+-----------+
| Tasmania        |    126118 |
| Capital Region  |    322723 |
| South Australia |    978100 |
| West Australia  |   1096829 |
| Queensland      |   1291117 |
| Victoria        |   2865329 |
| New South Wales |   3276207 |
+-----------------+-----------+

This orders the results in ascending order, which lists the minimum value first.

Note that, when ordering by a multi-word alias (like `Max Value`), you need to use the backtick character (`) instead of the apostrophe (') to surround the two words.

Find the Maximum Character Length

The MAX() function isn’t limited to just columns with numerical data. You can also combine MAX() with other functions to return maximum values in other areas.

For example, using our sample data, we can find the value with the maximum number of characters in the City column:

SELECT MAX(CHAR_LENGTH(Name)) AS 'Maximum Character Length'
FROM city;

Result:

+--------------------------+
| Maximum Character Length |
+--------------------------+
|                       34 |
+--------------------------+

We can also see this by using the following query (which doesn’t involve the MAX() function):

SELECT Name, CHAR_LENGTH(Name) AS 'Character Length'
FROM city
ORDER BY `Character Length` DESC
LIMIT 10;

Result:

+--------------------------------------+------------------+
| Name                                 | Character Length |
+--------------------------------------+------------------+
| Luxembourg [Luxemburg/Lëtzebuerg]   |               34 |
| Castellón de la Plana [Castell      |               31 |
| San Fernando del Valle de Cata       |               30 |
| Santo Domingo de los Colorados       |               30 |
| Thiruvananthapuram (Trivandrum       |               30 |
| [San Cristóbal de] la Laguna        |               29 |
| Ingraj Bazar (English Bazar)         |               28 |
| Soledad de Graciano Sánchez         |               28 |
| Valle de Chalco Solidaridad          |               27 |
| Machilipatnam (Masulipatam)          |               27 |
+--------------------------------------+------------------+

Using an OVER Clause

As mentioned, the syntax allows for an OVER clause to be included in your queries. Basically, the OVER clause allows you to specify how to partition query rows into groups for processing by the window function.

Here’s an example:

SELECT 
    District,
    Name AS City,
    Population AS 'City Population',
    MAX(Population) OVER(PARTITION BY District) AS 'District Max'
FROM City
WHERE CountryCode = 'AUS'
ORDER BY `District Max` DESC;

Result:

+-----------------+---------------+-----------------+--------------+
| District        | City          | City Population | District Max |
+-----------------+---------------+-----------------+--------------+
| New South Wales | Sydney        |         3276207 |      3276207 |
| New South Wales | Wollongong    |          219761 |      3276207 |
| New South Wales | Newcastle     |          270324 |      3276207 |
| New South Wales | Central Coast |          227657 |      3276207 |
| Victoria        | Melbourne     |         2865329 |      2865329 |
| Victoria        | Geelong       |          125382 |      2865329 |
| Queensland      | Townsville    |          109914 |      1291117 |
| Queensland      | Brisbane      |         1291117 |      1291117 |
| Queensland      | Cairns        |           92273 |      1291117 |
| Queensland      | Gold Coast    |          311932 |      1291117 |
| West Australia  | Perth         |         1096829 |      1096829 |
| South Australia | Adelaide      |          978100 |       978100 |
| Capital Region  | Canberra      |          322723 |       322723 |
| Tasmania        | Hobart        |          126118 |       126118 |
+-----------------+---------------+-----------------+--------------+

This example partitions the rows by District, providing the maximum value for each partition (district). This allows you to see more granular data, such as each city’s population, along with the population of the largest city in the same district.