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.
SELECT MIN(price) AS cheapest FROM books;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- PostgreSQL, MySQL, Oracle: smallest of several columns in one row SELECT LEAST(price, 20) FROM books;
Usage notes
- NULLs are skipped.
MINof a column with someNULLs is the smallest real value; if every value isNULL, the answer isNULL. - 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-15sorts correctly; a format like15/03/2024does not. - The row, not just the value.
SELECT title, MIN(price)is invalid in standard SQL becausetitleis 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 asROW_NUMBER(). - Empty table. An aggregate without
GROUP BYon an empty set returns one row withNULL; withGROUP BYit returns no rows. - Indexes.
MINon an indexed column can be answered by reading the first entry of the index, so it is very cheap. DISTINCTis pointless here: the minimum of the distinct values equals the minimum of all values.
Related
- MAX(), AVG(), COUNT(), GROUP BY, ORDER BY
- Lessons: Aggregate functions, Subqueries