Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT MAX(price, 10) AS price_at_least_ten
FROM products;

Try it

SQL Capping and flooring values ยท database: shop

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

SQL NULL, text and mixed types

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

Syntax

SQL
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
SQL
-- 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 ALL and use the aggregate, or use a CASE expression (CASE).