In MariaDB, MAX() is an aggregate function that returns the maximum value in a given expression.

Syntax

The syntax goes like this:

MAX([DISTINCT] expr)

Sample Data

Suppose we have a table with the following data:

SELECT
    VendorId,
    ProductName,
    ProductPrice
FROM Products;

Result:

+----------+---------------------------------+--------------+
| VendorId | ProductName                     | ProductPrice |
+----------+---------------------------------+--------------+
|     1001 | Left handed screwdriver         |        25.99 |
|     1001 | Right handed screwdriver        |        25.99 |
|     1001 | Long Weight (blue)              |        14.75 |
|     1001 | Long Weight (green)             |        11.99 |
|     1002 | Sledge Hammer                   |        33.49 |
|     1003 | Chainsaw                        |       245.00 |
|     1003 | Straw Dog Box                   |        55.99 |
|     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |
+----------+---------------------------------+--------------+

Example

We can use the following query to get the maximum price from that table.

SELECT MAX(ProductPrice)
FROM Products;

Result:

245.00

In this case, price information is stored in the ProductPrice column, and so we pass that as an argument to the MAX() function, which then calculates and returns the result.

Filtered Results

The MAX() function operates on the rows returned by the query. So if you filter the results, the result of MAX() will reflect that.

SELECT MAX(ProductPrice)
FROM Products
WHERE VendorId = 1001;

Result:

25.99

In this case, 25.99 is the maximum price out of all the products offered by the specified vendor.

Date/Time Data

You can use MAX() on date/time values.

Suppose we have the following table:

SELECT PetName, DOB 
FROM Pets;

Result:

+---------+------------+
| PetName | DOB        |
+---------+------------+
| Fluffy  | 2020-11-20 |
| Fetch   | 2019-08-16 |
| Scratch | 2018-10-01 |
| Wag     | 2020-03-15 |
| Tweet   | 2020-11-28 |
| Fluffy  | 2020-09-17 |
| Bark    | NULL       |
| Meow    | NULL       |
+---------+------------+

We can use MAX() to find the maximum date of birth (DOB).

SELECT MAX(DOB)
FROM Pets;

Result:

2020-11-28

This would be the date of birth of the youngest pet.

NULL Values

The MAX() function ignores any NULL values. In our sample table above, the last two rows have got NULL values in their DOB column, but that was ignored in our MAX() example.

Character Data

When used with character data columns, MAX() finds the value that is highest in the collating sequence.

Example:

SELECT MAX(ProductName)
FROM Products;

Result:

Straw Dog Box

The DISTINCT Keyword

The DISTINCT keyword can be used with the MAX() function, however, this produces the same result as omitting DISTINCT. Allowing the DISTINCT function enables MariaDB to comply with the ISO SQL standard.

Therefore, we can do this:

SELECT MAX(DISTINCT ProductPrice)
FROM Products;

Result:

245.00

But it has no impact on the results.

The DISTINCT keyword can be very useful when used with other contexts (e.g. with the COUNT() function), but it has no meaning when used with MAX(). The DISTINCT keyword removes duplicates, but in the case of MAX() it won’t make any difference because MAX() returns the same result regardless of how many rows share the same maximum value.

Window Functions

The MAX() function can be used in conjunction with the OVER clause to create a window function.

See SQL MAX() For Beginners for an example.