SQL MAX Function

Summary: in this tutorial, you will learn about the SQL MAX function that returns the maximum value in a set.

Introduction to SQL MAX function

The MAX() function returns the maximum value in a set. The MIN function does not take the NULL values into the evaluation. The following illustrates how to use the  MAX() function:

MAX(DISTINCT or ALL expression)Code language: SQL (Structured Query Language) (sql)

The ALL modifier instructs the MAX function to find the maximum value in all values including duplicates. The ALL modifier is used by default so you don not have to specify it explicitly.

Similar to the MIN function, the DISTINCT modifier is not applicable to the MAX() function and is only for ISO compatibility.

SQL MAN function examples

We will use the  products table in the sample database for the demonstration.

Simple MAX function usages

To find the highest unit price of products, you use the MAX() function as the following query:

SELECT 
    MAX(unitprice)
FROM
    products;Code language: SQL (Structured Query Language) (sql)

To get the most expensive products, you need to use a subquery  as follows:

SELECT 
    productid, productname, unitprice
FROM
    products
WHERE
    unitprice = (
        SELECT 
            MAX(unitprice)
        FROM
            products);Code language: SQL (Structured Query Language) (sql)

The subquery returns the highest unit price of the products. Based on the highest price, the outer query selects the product data including product id, product name and unit price.

SQL MAX function with GROUP BY example

The following query selects the highest unit price of product in each product’s category:

SELECT 
    categoryid, MAX(unitprice)
FROM
    products
GROUP BY categoryid;Code language: SQL (Structured Query Language) (sql)

The GROUP BY clause divides the products by categoryid into groups. For each group, the MAX() function returns the highest unit price.

SQL MAX function with HAVING clause example

To get the category that has the highest unit price greater than $100, you can combine the MAX() function with the GROUP BY and HAVING clauses as the following query:

SELECT 
    categoryid, MAX(unitprice) AS maxprice
FROM
    products
GROUP BY categoryid
HAVING maxprice > 100;Code language: SQL (Structured Query Language) (sql)

Notice that a column alias is used in both SELECT and HAVING clauses.

In this tutorial, you have learned how to use the SQL MAX function to find the maximum value in a set.