Summary: in this tutorial, you will learn how to use the SQL MIN function to get the minimum value in a set.
Introduction to SQL MIN function
MIN function returns the minimum value in a set of values. The
MIN function ignores the
NULL values. The following is the syntax of the
MIN(DISTINCT or ALL expression)
ALL modifier instructs the
MIN function to find the minimum value in all values including duplicates. The
MIN() function uses the
ALL modifier by default so you don’t have to specify it explicitly.
SQL MIN function examples
Let’s take a look at the
products table in the sample database:
Simple MIN function example
To find the lowest unit price of products in the
products table, you use the
MIN() function as follows:
SELECT MIN(unitprice) FROM products;
To get the cheapest products, you have to use a subquery that uses the
MIN() function as the following query:
SELECT productid, productname, unitprice FROM products WHERE unitprice = ( SELECT MIN(unitprice) FROM products);
The outer query gets the cheapest products whose unit prices match the lowest price returned by the subquery. If multiple products have the same unit price as the lowest price, the query will return more than one row.
SQL MIN function with GROUP BY clause example
To find the lowest unit price of the product in each category, you use the
MIN() function with a GROUP BY clause:
SELECT categoryid, MIN(unitprice) FROM products GROUP BY categoryid;
GROUP BY clause divides the products by
categoryid into groups. For each group, the
MIN() function returns the lowest unit price.
SQL MIN function with HAVING clause example
To get the category that has the lowest unit price less than $7, you use the
MIN() function with the
GROUP BY and HAVING clauses as follows:
SELECT categoryid, MIN(unitprice) FROM products GROUP BY categoryid HAVING MIN(unitprice) < 7;
SQL MIN with correlated subquery example
To get the cheapest product in each category, you use the
MIN() function in a correlated subquery as follows:
SELECT categoryid, productid, productName, unitprice FROM products a WHERE unitprice = ( SELECT MIN(unitprice) FROM products b WHERE b.categoryid = a.categoryid)
The outer query scans all rows in the
products table and returns the products that have unit prices match the lowest price in each category returned by the correlated subquery.
In this tutorial, we have shown you how to use SQL MIN function to get the minimum value in a set.