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)

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.

products table

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;
SQL MAX example

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

SQL MAX Subquery

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;
SQL MAX GROUP BY example

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: PHP (php)
SQL MAX HAVING example

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.