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:

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:

To get the cheapest products, you have to use a subquery that uses the `MIN()` function as the following query:

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 product in each category, you use the `MIN()` function with a GROUP BY clause:

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 lowest unit price less than \$7, you use the `MIN()` function with the `GROUP BY` and HAVING clauses as follows:

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:

The outer query scans all rows in the `products `table and returns the products that have unit prices match with 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.