SQL MIN() Function

Learn how to use SQL MIN() to find the smallest non-NULL value, filter minimums with WHERE, group results with GROUP BY, and find earliest dates.

The SQL MIN() function returns the smallest value in a column or expression. It is an aggregate function, which means it calculates a result from multiple rows.

A table is a database object made of rows and columns. A column is a named field that stores values of a particular kind. When you use MIN() without grouping, the query normally returns one result for the complete set of eligible rows.

What Does MIN() Do?

MIN() examines the selected values and returns the smallest non-NULL value. For numeric data, this is the lowest number. For date or time data, it is the earliest value.

For example, if a column contains 25.00, 10.50, and 18.75, MIN() returns 10.50. Without a GROUP BY clause, the query produces one result for the queried set.

Basic MIN() Syntax

SELECT MIN(column_name)
FROM table_name;

SELECT starts a query that retrieves data.
MIN() is the aggregate function that finds the smallest value.
column_name is the column or expression being evaluated.
FROM table_name identifies the table containing the data.

For readable output, assign the result an alias. An alias is a temporary label for a query result column.

SELECT MIN(amount) AS lowest_amount
FROM products;

Aliases are explained further in SQL aliases.

Example: Find the Lowest Product Amount

Assume a products table with these columns:

product_id: an integer identifier
product_name: a text description
category: a text category
amount: a decimal price or amount

Example rows might contain amounts of 24.99, 8.50, 15.00, and 42.00. The following query finds the lowest stored amount:

SELECT MIN(amount) AS lowest_amount
FROM products;

Example result:

lowest_amount
-------------
8.50

The query returns one row and one column. The value 8.50 is the lowest non-NULL amount in the table.

Understanding the Result Column

Without an alias, many database systems label the result with the aggregate expression itself:

SELECT MIN(amount)
FROM products;

MIN(amount)
-----------
8.50

Using AS lowest_amount gives the result a clearer label. The alias changes the displayed column name; it does not change the data.

Using MIN() with WHERE

WHERE filters rows before an aggregate function is evaluated. Therefore, a WHERE condition changes the set of values that MIN() examines. See SQL WHERE clause for more filtering techniques.

SELECT MIN(amount) AS lowest_amount
FROM products
WHERE category = 'Office Supplies';

This query returns the lowest amount only among products whose category is Office Supplies. It does not compare those products with products in other categories.

For example, if the matching amounts are 12.00, 8.50, and 19.99, the result is 8.50, even if another category contains a lower amount.

Using MIN() with GROUP BY

GROUP BY divides rows into groups so that an aggregate is calculated separately for each group. This is different from calculating one overall minimum.

SELECT category, MIN(amount) AS lowest_amount
FROM products
GROUP BY category;

Example result:

category          lowest_amount
----------------  -------------
Office Supplies   8.50
Electronics       24.99

This query returns one row per category. The category column identifies the group, and MIN(amount) finds the lowest amount within that group.

Without GROUP BY: one minimum across all eligible products.
With GROUP BY category: one minimum for each category.

When selecting a nonaggregated column together with an aggregate, that column generally must be included in GROUP BY. For example, category belongs in GROUP BY category in the query above.

NULL Values and MIN()

NULL represents a missing or unknown value. MIN() ignores NULL values when one or more non-NULL values are available.

If the amounts are 12.00, NULL, and 8.50, the minimum is 8.50. The missing value is not treated as zero.

If every eligible amount is NULL, or if a WHERE condition excludes every row with a non-NULL amount, the result of MIN() is NULL.

Important: NULL is not the same as zero, an empty string, or a missing row. Do not expect MIN() to replace missing values with zero.

Data Types and Ordering

Numbers: MIN() commonly finds the lowest integer, decimal, or other numeric value.
Dates and times: MIN() finds the earliest date or time.
Text: MIN() compares text according to the database's collation and sort rules. The smallest text value may therefore depend on case handling, accents, and the configured collation.

Finding the Earliest Order Date

For an orders table containing an order_date column, use:

SELECT MIN(order_date) AS first_order_date
FROM orders;

This returns one row containing the earliest non-NULL order date. The rows do not need to be stored in chronological order for MIN() to find the earliest date.

MIN() Compared with Related Aggregate Functions

MIN(): returns the smallest non-NULL value.
MAX(): returns the largest non-NULL value. See the SQL MAX() function.
SUM(): adds numeric values to produce a total; it does not select the lowest value. See the SQL SUM() function.

Other useful aggregate functions include COUNT() for counting rows or values and AVG() for calculating an average.

Common Troubleshooting Problems

The Query Returns NULL

All eligible values may be NULL, or the WHERE condition may have excluded every row with a non-NULL value. Inspect the filtered rows and verify that the target column contains at least one non-NULL value.

The Result Is Not a Minimum for Each Category

If you use MIN() without GROUP BY, SQL calculates one minimum across the entire result set. To calculate one per category, select category and add GROUP BY category.

The Minimum Changed After Adding WHERE

This is expected when the condition removes rows from consideration. Review the WHERE expression and inspect which rows match it.

A Text Minimum Looks Unexpected

Text comparison follows the database's collation and case-handling rules rather than a universal alphabetical order. Check the active collation and use an appropriate normalized expression or collation when your database supports it.

A Nonaggregated Column Causes an Error

A selected column that is not inside an aggregate generally must appear in GROUP BY. Remove an unrelated column, aggregate it appropriately, or add it to GROUP BY when separate grouping is intended.

Quick Reference

-- Overall minimum
SELECT MIN(amount) AS lowest_amount
FROM products;

-- Minimum after filtering
SELECT MIN(amount) AS lowest_amount
FROM products
WHERE category = 'Office Supplies';

-- Minimum for each category
SELECT category, MIN(amount) AS lowest_amount
FROM products
GROUP BY category;

-- Earliest date
SELECT MIN(order_date) AS first_order_date
FROM orders;

For the surrounding query concepts, review SQL SELECT statements, SQL functions, and the SQL ORDER BY clause.