SQL MIN Function

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

The 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 function:

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

The 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.

Unlike other aggregate functions e.g., SUM, COUNT, and AVG, the DISTINCT modifier is not applicable to the MIN() function. The DISTINCT modifier is only for ISO compatibility.

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;Code language: SQL (Structured Query Language) (sql)

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);Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

The 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;Code language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

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.