Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Aggregate Functions

SQL MIN() function

Reference for the SQL MIN() aggregate: smallest number, earliest date or alphabetically first text, NULL handling, and finding the row that holds it.

MIN(expr) is an aggregate function that returns the smallest non-NULL value of expr in a group of rows. It works on numbers, on dates (earliest) and on text (first in the sort order of the column's collation).

Like the other aggregates, it collapses many rows into one value per group. It tells you the minimum, but not which row holds it; the usage notes show how to get the whole row.

SQL
SELECT MIN(price) AS cheapest
FROM books;

Try it

SQL Smallest and earliest values ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL One minimum per genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Finding the whole row with the minimum ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
MIN(expr)
MIN(DISTINCT expr)         -- allowed, same result as MIN(expr)
MIN(expr) OVER (...)       -- as a window function
Part Description
expr A column or expression of a comparable type: number, date/time or text. NULLs are ignored.

Returns: the smallest non-NULL value, with the same type as expr, or NULL if the group is empty or all values are NULL.

Dialect differences

Database Notes
SQLite Also has a scalar form with two or more arguments: MIN(3, 7, 5) returns 3 (and NULL if any argument is NULL). With one argument it is the aggregate. Mixed types compare by storage class: numbers before text.
PostgreSQL Aggregate only. The scalar equivalent is LEAST(a, b, c), which ignores NULLs.
MySQL Aggregate only; LEAST() is the scalar form and returns NULL if any argument is NULL.
SQL Server Aggregate only. LEAST() exists from SQL Server 2022.
Oracle Aggregate only. LEAST() returns NULL if any argument is NULL.
SQL
-- PostgreSQL, MySQL, Oracle: smallest of several columns in one row
SELECT LEAST(price, 20) FROM books;

Usage notes

  • NULLs are skipped. MIN of a column with some NULLs is the smallest real value; if every value is NULL, the answer is NULL.
  • Text depends on collation. MIN(title) may treat uppercase and lowercase differently from one database to another.
  • Dates only work as dates if stored consistently. ISO text such as 2024-03-15 sorts correctly; a format like 15/03/2024 does not.
  • The row, not just the value. SELECT title, MIN(price) is invalid in standard SQL because title is not aggregated (SQLite and old MySQL tolerate it and return an arbitrary or matching row, which is a portability trap). Use a subquery, ORDER BY ... LIMIT 1, or a window function such as ROW_NUMBER().
  • Empty table. An aggregate without GROUP BY on an empty set returns one row with NULL; with GROUP BY it returns no rows.
  • Indexes. MIN on an indexed column can be answered by reading the first entry of the index, so it is very cheap.
  • DISTINCT is pointless here: the minimum of the distinct values equals the minimum of all values.