SQL Reference ยท Scalar Functions
SQL MIN() and MAX() with several arguments
Reference for MIN() and MAX() with two or more arguments in SQLite, compared with GREATEST() and LEAST() in other databases, including NULL handling.
With a single argument, MIN(x) and MAX(x) are aggregate functions: they look down a column and return one value for the whole group. In SQLite, giving them two or more arguments turns them into scalar functions that compare values within one row and return the smallest or largest.
That is the SQLite way of writing what the standard-leaning databases call LEAST() and GREATEST(). It is perfect for capping a value, picking the later of two dates or choosing a fallback price.
SELECT MAX(price, 10) AS price_at_least_ten FROM products;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
MIN(a, b [, c, ...]) MAX(a, b [, c, ...])
| Name | Description |
|---|---|
a, b, ... |
Two or more expressions to compare. |
Returns: the smallest (MIN) or largest (MAX) argument. If any argument is NULL, the result is NULL. With exactly one argument the call is the aggregate version instead.
Dialect differences
| Database | Function | NULL arguments |
|---|---|---|
| SQLite | MIN(a, b, ...), MAX(a, b, ...) |
Result is NULL if any argument is NULL |
| PostgreSQL | LEAST(...), GREATEST(...) |
NULLs are ignored; NULL only if all are NULL |
| MySQL | LEAST(...), GREATEST(...) |
Result is NULL if any argument is NULL |
| SQL Server | LEAST(...), GREATEST(...) since SQL Server 2022; before that use CASE or a VALUES subquery |
NULLs are ignored |
| Oracle | LEAST(...), GREATEST(...) |
Result is NULL if any argument is NULL |
-- PostgreSQL, MySQL, Oracle, SQL Server 2022+ SELECT GREATEST(price, 10), LEAST(price, 50) FROM products; -- SQL Server before 2022 SELECT (SELECT MAX(v) FROM (VALUES (price), (10)) AS t(v)) FROM products;
Usage notes
- One argument or two decides everything.
MAX(price)is the aggregate;MAX(price, 0)is the scalar. A typo that drops the second argument silently changes the query into an aggregate. - NULL wins in SQLite. Use
COALESCE()to give each argument a default first:MAX(COALESCE(a, 0), COALESCE(b, 0)). - Comparison follows the usual type order: numbers sort before text, so
MAX(10, '9')returns'9'. Compare like with like. - ISO dates compare correctly as text, which makes
MAX(date_a, date_b)the later date. - Clamping a value into a range is
MIN(MAX(x, low), high). - For several rows or columns at once, unpivot with
UNION ALLand use the aggregate, or use aCASEexpression (CASE).
Related
MIN()andMAX()aggregates,COALESCE(),CASE,IIF()- Lessons: Aggregate functions, Numeric functions