Conditional

LEAST

Returns the smallest value in a list.

Syntax

LEAST(<value1>, <value2>, …)

Compares several values in the same row and returns the smallest. Its mirror function is GREATEST, which returns the largest. Don't confuse it with MIN: that one searches across many rows, while LEAST looks at values side by side. Handy for capping a number: “no more than”.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

orders

id	created_at	amount
1	2026-07-03 10:15:00	120.50
2	2026-07-28 18:40:00	80.25
3	2026-08-09 09:05:00	199.99
4	2026-08-21 14:30:00	45.00
5	2026-09-02 11:00:00	64.75

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The smallest in a list

SELECT LEAST(3, 7, 5) AS smallest;

smallest
3

A coupon covers at most 100

SELECT id, amount,

LEAST(amount, 100) AS covered_by_coupon

FROM orders;

id	amount	covered_by_coupon
1	120.50	100
2	80.25	80.25
3	199.99	100
4	45.00	45.00
5	64.75	64.75

A bonus of at most 200 — careful with NULL

SELECT name, bonus,

LEAST(bonus, 200) AS capped_bonus

FROM employees;

name	bonus	capped_bonus
Emma	300	200
Liam	NULL	200
Olivia	150	150
Noah	NULL	200
Sophia	200	200

Liam and Noah had no bonus, and now it's 200: LEAST skips NULL. If a missing bonus should stay missing, check it first: CASE WHEN bonus IS NULL THEN NULL ELSE LEAST(bonus, 200) END.

Limits on both sidesAdvanced

SELECT id, amount,

LEAST(GREATEST(amount, 50), 150) AS clamped

FROM orders;

id	amount	clamped
1	120.50	120.50
2	80.25	80.25
3	199.99	150
4	45.00	50
5	64.75	64.75

First GREATEST raises an amount that's too small to 50, then LEAST lowers one that's too big to 150.

Watch out

PostgreSQL skips NULL: LEAST(5, NULL) gives 5, and only when every value is NULL is the result NULL. In MySQL or Oracle, any NULL would give NULL.

The values must have compatible types: LEAST(5, 'abc') throws invalid input syntax for type integer.

To find the smallest value across table rows, use MIN. LEAST(salary) with a single argument just returns each row's salary.

Related functions

GREATEST MIN COALESCE