SQL Server MIN Function

Summary: in this tutorial, you will learn how to use the SQL Server MIN() function to find the minimum value in a set.

Introduction to SQL Server MIN() function

In SQL Server, the MIN() function is an aggregate function that allows you to find the minimum value in a set.

The following illustrates the syntax of the MIN() function:

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

The MIN() function accepts an expression that can be a column or a valid expression.

The MIN() function applies to all values including duplicates. It means that the DISTINCT modifier does not affect the MIN() function. Note that the MIN() function ignores NULL.

SQL Server MIN() function examples

We will use the products and categories tables from the sample database for the demonstration.

SQL Server MIN Function - Products & Categories Tables

1) Basic SQL Server MIN() function example

The following example uses the MIN() function to find the lowest list price of all products in the production.products table:

SELECT
    MIN(list_price) min_list_price
FROM
    production.products;Code language: SQL (Structured Query Language) (sql)

Output:

min_list_price
--------------
89.99
(1 row)Code language: CSS (css)

The following example uses the MIN() function in a subquery to find the products with the lowest prices:

SELECT
  product_id,
  product_name,
  list_price
FROM
  production.products
WHERE
  list_price = (
    SELECT
      MIN(list_price)
    FROM
      production.products
  );Code language: SQL (Structured Query Language) (sql)

Output:

product_id | product_name                           | list_price
-----------+----------------------------------------+-----------
263        | Strider Classic 12 Balance Bike - 2018 | 89.99
(1 row)

In this example:

First, the subquery uses the MIN() function to return the lowest list price.
Then, the outer query finds the products whose list prices equal the lowest price.

Note that the query will return multiple rows if the products table has multiple rows whose values in the list_price column are 89.99.

2) Using the MIN() function with GROUP BY clause example

The following statement uses the MIN() function with the GROUP BY clause to find the lowest list price within each product category:

SELECT
  category_name,
  MIN(list_price) min_list_price
FROM
  production.products p
  INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
  category_name
ORDER BY
  category_name;Code language: SQL (Structured Query Language) (sql)

Output:

category_name       | min_list_price
--------------------+----------------
Children Bicycles   | 89.99
Comfort Bicycles    | 416.99
Cruisers Bicycles   | 250.99
Cyclocross Bicycles | 1549.00
Electric Bikes      | 1559.99
Mountain Bikes      | 379.99
Road Bikes          | 749.99
(7 rows)

In this example:

First, the GROUP BY clause divides the products into groups by category name.
Second, apply the MIN() function to each group to find the lowest list price in each category.

3) Using the MIN() function with HAVING clause example

The following example uses the MIN() function in the HAVING clause to retrieve the product category with a minimum list price greater than 500:

SELECT
  category_name,
  MIN(list_price) min_list_price
FROM
  production.products p
  INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
  category_name
HAVING
  MIN(list_price) > 500
ORDER BY
  category_name;Code language: SQL (Structured Query Language) (sql)

Output:

category_name       | min_list_price
--------------------+---------------
Cyclocross Bicycles | 1549.00
Electric Bikes      | 1559.99
Road Bikes          | 749.99
(3 rows)

4) Using the MIN() function with a date column

The following statement uses the MIN() function with the required_date column of the sales.orders table to get the earliest required date of all orders:

SELECT
  MIN(required_date) earliest_required_date
FROM
  sales.orders;Code language: SQL (Structured Query Language) (sql)

Output:

earliest_required_date
----------------------
2016-01-03

The following example uses the MIN() function to find the earliest order dates by sales staff:

SELECT
  s.first_name,
  MIN(required_date) earliest_required_date
FROM
  sales.orders o
  INNER JOIN sales.staffs s ON s.staff_id = o.order_id
GROUP BY
  s.first_name
ORDER BY
  earliest_required_date;Code language: SQL (Structured Query Language) (sql)

Output:

first_name | earliest_required_date
-----------+-----------------------
Fabiola    | 2016-01-03
Mireya     | 2016-01-04
Virgie     | 2016-01-04
Genna      | 2016-01-05
Kali       | 2016-01-05
Jannette   | 2016-01-06
Bernardine | 2016-01-06
Marcelene  | 2016-01-07
Venita     | 2016-01-07
Layla      | 2016-01-08
(10 rows)

5) Using the MIN() function with the text column

The following example uses the MIN() function to return the first product name sorted alphabetically within each category:

SELECT
  c.category_name category_name,
  MIN(product_name) product_name
FROM
  production.products p
  INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
  c.category_name;Code language: SQL (Structured Query Language) (sql)

Output:

category_name       | product_name
--------------------+------------------------------------------------
Children Bicycles   | Electra Cruiser 1 (24-Inch) - 2016
Comfort Bicycles    | Electra Townie Balloon 3i EQ - 2017/2018
Cruisers Bicycles   | Electra Amsterdam Fashion 3i Ladies' - 2017/2018
Cyclocross Bicycles | Surly Straggler - 2016
Electric Bikes      | Electra Loft Go! 8i - 2018
Mountain Bikes      | Haro Flightline One ST - 2017
Road Bikes          | Surly ECR - 2018
(7 rows)

Summary

Use the MIN() function to find the minimum value in a set of values.
The MIN() function ignores NULL.

Was this tutorial helpful?