SQL MAX() for Beginners

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

Below are some basic examples to demonstrate how it works.

Sample Table

Suppose we have the following table:

SELECT * FROM Products;

Result:

+-------------+------------+---------------------------------+----------------+-----------------------------------------+
| ProductId   | VendorId   | ProductName                     | ProductPrice   | ProductDescription                      |
|-------------+------------+---------------------------------+----------------+-----------------------------------------|
| 1           | 1001       | Left handed screwdriver         | 25.99          | Purple. Includes left handed carry box. |
| 2           | 1001       | Long Weight (blue)              | 14.75          | Includes a long wait.                   |
| 3           | 1001       | Long Weight (green)             | 11.99          | Approximate 30 minute waiting period.   |
| 4           | 1002       | Sledge Hammer                   | 33.49          | Wooden handle. Free wine glasses.       |
| 5           | 1003       | Chainsaw                        | 245.00         | Orange. Includes spare fingers.         |
| 6           | 1003       | Straw Dog Box                   | NULL           | Tied with vines. Very chewable.         |
| 7           | 1004       | Bottomless Coffee Mugs (4 Pack) | 9.99           | Brown ceramic with solid handle.        |
+-------------+------------+---------------------------------+----------------+-----------------------------------------+

Example

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

SELECT MAX(ProductPrice)
FROM Products;

Result:

+--------------------+
| (No column name)   |
|--------------------|
| 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.

Using Column Aliases

You’ll notice that the previous results don’t include a column name. Some DBMSs may use something like MAX(ProductPrice) as the column name. This is to be expected, because the MAX() function doesn’t return any columns. You can easily provide a column name by assigning an alias.

SELECT MAX(ProductPrice) AS MaximumPrice
FROM Products;

Result:

+----------------+
| MaximumPrice   |
|----------------|
| 245.00         |
+----------------+

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) AS MaximumPrice
FROM Products
WHERE VendorId = 1001;

Result:

+----------------+
| MaximumPrice   |
|----------------|
| 25.99          |
+----------------+

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

`NULL` Values

The MAX() function ignores any NULL values. In our sample table above, product number 6 has got NULL in its ProductPrice column, but that was ignored in our MAX() example.

Depending on your DBMS and your settings, you may or may not see a warning that NULL values were eliminated in the result set.

Here’s an example of what you might see:

SELECT MAX(ProductPrice) AS MaximumPrice
FROM Products;

Result:

+----------------+
| MaximumPrice   |
|----------------|
| 245.00         |
+----------------+
Warning: Null value is eliminated by an aggregate or other SET operation.

All this tells us is that the column contained at least one NULL value, and that it was ignored when calculating the results.

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) AS MaxDOB
FROM Pets;

Result:

+------------+
| MaxDOB     |
|------------|
| 2020-11-28 |
+------------+

This would be the date of birth of the youngest pet. As mentioned, it ignores any NULL values.

Character Data

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

Example:

SELECT MAX(ProductName) AS MaximumProductName
FROM Products;

Result:

+----------------------+
| MaximumProductName   |
|----------------------|
| Straw Dog Box        |
+----------------------+

The `DISTINCT` Keyword

Your DBMS may allow the DISTINCT keyword to be used with the MAX() function. If so, this is probably in order to comply with the ISO SQL standard.

Therefore, you may be able to do this:

SELECT MAX(DISTINCT ProductPrice) AS MaximumPrice
FROM Products;

Result:

+----------------+
| MaximumPrice   |
|----------------|
| 245.00         |
+----------------+

But it will have 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

Depending on your DBMS, you may be able to use an OVER clause with your MAX() function to create a window function.

A window function performs an aggregate-like operation on a set of query rows. It produces a result for each query row. This is in contrast to an aggregate operation, which groups query rows into a single result row.

Here’s an example to demonstrate the concept.

We’ve already seen the Products table. Our database also has a Customers table, and it contains the following data:

+--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+
| CustomerId   | CustomerName         | PostalAddress     | City       | StateProvince   | ZipCode    | Country   | Phone          |
|--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+
| 1001         | Palm Pantry          | 20 Esplanade      | Townsville | QLD             | 2040       | AUS       | (308) 555-0100 |
| 1002         | Tall Poppy           | 12 Main Road      | Columbus   | OH              | 43333      | USA       | (310) 657-0134 |
| 1003         | Crazy Critters       | 10 Infinite Loops | Cairns     | QLD             | 4870       | AUS       | (418) 555-0143 |
| 1004         | Oops Media           | 4 Beachside Drive | Perth      | WA              | 1234       | AUS       | (405) 443-5987 |
| 1005         | Strange Names Inc.   | 789 George Street | Sydney     | NSW             | 2000       | AUD       | (318) 777-0177 |
| 1006         | Hi-Five Solutionists | 5 High Street     | Highlands  | HI              | 1254       | AUS       | (415) 413-5182 |
+--------------+----------------------+-------------------+------------+-----------------+------------+-----------+----------------+

We can retrieve data from these tables and present them as one result set by using a join.

We can also use the MAX() function with the OVER clause to apply a window function to the data.

SELECT 
    v.VendorName,
    p.ProductName,
    p.ProductPrice,
    MAX(ProductPrice) OVER (PARTITION BY v.VendorName) AS "Maximum Price For This Vendor"
FROM Products p 
INNER JOIN Vendors v 
ON v.VendorId = p.VendorId
ORDER BY VendorName, ProductPrice, "Maximum Price For This Vendor";

Result:

+---------------+---------------------------------+----------------+---------------------------------+
| VendorName    | ProductName                     | ProductPrice   | Maximum Price For This Vendor   |
|---------------+---------------------------------+----------------+---------------------------------|
| Katty Kittens | Bottomless Coffee Mugs (4 Pack) | 9.99           | 9.99                            |
| Mars Supplies | Long Weight (green)             | 11.99          | 25.99                           |
| Mars Supplies | Long Weight (blue)              | 14.75          | 25.99                           |
| Mars Supplies | Left handed screwdriver         | 25.99          | 25.99                           |
| Pedal Medals  | Straw Dog Box                   | NULL           | 245.00                          |
| Pedal Medals  | Chainsaw                        | 245.00         | 245.00                          |
| Randy Roofers | Sledge Hammer                   | 33.49          | 33.49                           |
+---------------+---------------------------------+----------------+---------------------------------+

In this case we used the OVER clause with our MAX() function to partition the result by vendor name.

By doing this, we were able to return price information for each product, as well as the maximum price for all products from that given vendor. This maximum price changes as the vendor changes (unless multiple vendors happen to have the same maximum price), but remains the same for all products from the same vendor.

This concept can also be applied to other aggregate functions in SQL, such as SUM(), MIN(), AVG(), and COUNT().

Sample Table

Example

Using Column Aliases

Filtered Results

NULL Values

Date/Time Data

Character Data

The DISTINCT Keyword

Window Functions

`NULL` Values

The `DISTINCT` Keyword