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