LEAST()

Syntax

How it works

LEAST() takes two or more expressions and returns the smallest of them. The comparison happens horizontally, across the columns of a single row, so LEAST(a, b, c) looks at the three values in one row and hands back the lowest. Its mirror image is GREATEST(), which returns the largest.

This is the part that trips people up: LEAST() is not an aggregate. MIN() scans down a single column over many rows and collapses them to one value; LEAST() scans across several columns within one row and leaves the row count unchanged. If you have three date columns and want the earliest date in each row, you want LEAST(), not MIN().

NULL handling differs by engine and is the second common surprise. In MySQL, LEAST() returns NULL if any argument is NULL. In PostgreSQL, LEAST() ignores NULL arguments and returns the smallest of the non-NULL values, only returning NULL when every argument is NULL. SQLite follows the PostgreSQL style of skipping NULLs. SQL Server did not have LEAST() until SQL Server 2022; on older versions you emulate it with a CASE expression or a derived table of values.

Examples

Earliest of several date columns (row-wise)

-- Pick the earliest known contact date per customer,
-- comparing three columns in the SAME row
SELECT id,
       LEAST(first_seen, last_login, signup_date) AS earliest
FROM customers;

id | earliest
---+-----------
 1 | 2025-01-04
 2 | 2024-11-30
 3 | 2026-02-18

Cap a value at an upper limit

-- Never let the applied discount exceed 100
SELECT id,
       LEAST(discount, 100) AS capped_discount
FROM orders;

id | capped_discount
---+----------------
 1 |              45
 2 |             100
 3 |              12

Lowest of three supplier quotes

SELECT part_no,
       LEAST(quote_a, quote_b, quote_c) AS best_price
FROM quotes;

part_no | best_price
--------+-----------
 P-100  |      12.50
 P-205  |       8.00
 P-311  |      41.75

NULL behaviour differs by engine

-- MySQL:      returns NULL (any NULL argument wins)
-- PostgreSQL: returns 5    (NULLs are ignored)
-- SQLite:     returns 5    (NULLs are ignored)
SELECT LEAST(10, NULL, 5) AS result;

result
------
(see note above: NULL on MySQL, 5 on PostgreSQL/SQLite)

Combine with COALESCE for consistent NULL handling

-- Treat missing quotes as a huge number so they
-- never win, giving the same answer on every engine
SELECT part_no,
       LEAST(
         COALESCE(quote_a, 999999),
         COALESCE(quote_b, 999999),
         COALESCE(quote_c, 999999)
       ) AS best_price
FROM quotes;

part_no | best_price
--------+-----------
 P-100  |      12.50
 P-205  |       8.00

Common mistakes

-- WRONG: trying to get the smallest value in a column.
-- LEAST does not aggregate; this errors or misbehaves
-- because it expects two or more arguments per row.
SELECT LEAST(price) FROM products;

-- To find the smallest value DOWN a column, use MIN,
-- which is an aggregate function.
SELECT MIN(price) FROM products;

-- On MySQL this returns NULL for every row that has
-- even one NULL date, silently dropping real answers.
SELECT LEAST(first_seen, last_login, signup_date)
FROM customers;

-- Guard each argument so a single NULL cannot poison
-- the result on MySQL.
SELECT LEAST(
         COALESCE(first_seen,  '9999-12-31'),
         COALESCE(last_login,  '9999-12-31'),
         COALESCE(signup_date, '9999-12-31')
       ) AS earliest
FROM customers;

-- Fails on SQL Server 2019 and earlier:
-- 'LEAST is not a recognized built-in function name'
SELECT LEAST(quote_a, quote_b, quote_c) AS best_price
FROM quotes;

-- Emulate LEAST on older SQL Server with a CASE
-- expression (or a VALUES derived table).
SELECT CASE
         WHEN quote_a <= quote_b AND quote_a <= quote_c THEN quote_a
         WHEN quote_b <= quote_c THEN quote_b
         ELSE quote_c
       END AS best_price
FROM quotes;

Performance

LEAST() is a lightweight scalar function: it compares its arguments for the current row and returns one of them, so its cost is trivial next to reading the row itself. There is no sort, hash or grouping involved the way there is with an aggregate.

Because LEAST() wraps the columns in an expression, a predicate such as WHERE LEAST(a, b) < 10 is generally not sargable and cannot use a plain index on a or b. If you filter on it often, consider a computed or generated column that stores the LEAST result and index that column instead.

Wrapping every argument in COALESCE for cross-engine NULL safety adds a small per-row cost, but it is usually worth it for correctness. Keep the sentinel value (for example a far-future date) well outside your real data range so it never wins the comparison.

Interview questions

SELECT LEAST(10, NULL, 5) AS result;  -- NULL on MySQL, 5 on PostgreSQL/SQLite

SELECT LEAST(GREATEST(x, 0), 100) AS clamped FROM t;