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
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(DISTINCT or ALL expression)
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;
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);
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;
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:
Code language: PHP (php)
SELECT categoryid, MAX(unitprice) AS maxprice FROM products GROUP BY categoryid HAVING maxprice > 100;
Notice that a column alias is used in both
In this tutorial, you have learned how to use the SQL
MAX function to find the maximum value in a set.