conditional
GREATEST / LEAST
Return the largest (GREATEST) or smallest (LEAST) of the provided arguments row-by-row — not across rows, which is MIN/MAX. NULL handling differs by engine: Postgres ignores NULL args, MySQL returns NULL if any argument is NULL.
GREATEST(a, b, ...) | LEAST(a, b, ...)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| LEAST | Mirror function returning the smallest argument |
| COALESCE around args | MySQL workaround to avoid NULL propagation |
| GREATEST(x, floor) | Clamp x to a minimum value in one call |
Examples
SELECT GREATEST(3, 7, 2);Returns 7
SELECT LEAST(quantity, stock_left) FROM cart;Cap requested quantity at available stock per row
SELECT GREATEST(created_at, updated_at) FROM records;Latest of two timestamps per row
SELECT GREATEST(1, NULL, 2);Postgres returns 2; MySQL returns NULL
Dialect notes / Gotcha
SQLite does NOT have GREATEST/LEAST — the scalar forms of MIN(a,b,c) and MAX(a,b,c) do the same job. Postgres and MySQL both support GREATEST/LEAST natively.
Related functions
CASE WHEN
SQL's portable if/else-if expression. Evaluates each WHEN clause in order and returns the first matching result; returns NULL from an unmatched CASE without ELSE.
COALESCE
Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.
MAX
Returns the largest non-NULL value in a group by the column's natural ordering. Useful for latest timestamps, top prices, or alphabetically last strings.
NULLIF
Returns NULL if value1 equals value2, otherwise returns value1. Handy for converting sentinel values (empty strings, 0) into NULL, or safely guarding against division by zero.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet