LEAST

Return the smallest value from a list of expressions.

Category: conditionalReturns: ANYDialect: Standard

Syntax

LEAST(expr1, expr2, ...)

Description

## Overview Returns the smallest value among its arguments according to the ordering rules of the resolved common supertype. LEAST accepts two or more arguments and works on any orderable type. Use LEAST to compute per-row minima across columns, to cap a value to a maximum ceiling, or to pick the earliest timestamp across multiple candidate columns. ## Behavior - Evaluates all arguments (non short-circuit). - If any argument is NULL, LEAST returns NULL. This is the NULL-propagating convention; other engines skip NULLs, so cross-engine migrations require care. - All arguments must be coercible to a common supertype; mismatches raise a type error. - String ordering follows the session collation. - Returns the argument's value, not a copy. ## Compatibility - LEAST is part of ANSI SQL:2003. Engines differ on NULL handling: some propagate (NULL anywhere returns NULL), others skip NULLs. DeltaForge propagates NULL. When porting queries from engines with skip-NULL semantics, wrap arguments with COALESCE to a sentinel that will lose comparison.

Parameters

Name	Type	Description
`expr`		Specifies two or more expressions to compare. All expressions must be coercible to a common supertype. Supports numeric, STRING, DATE, TIMESTAMP, and other orderable types.

Examples

-- Basic numeric comparison
SELECT LEAST(10, 20, 30) AS v;  -- 10

-- String comparison by collation
SELECT LEAST('apple', 'banana', 'cherry') AS v;  -- 'apple'

-- NULL propagates to NULL in DeltaForge
SELECT LEAST(1, NULL, 3) AS v;  -- NULL

-- Date comparison
SELECT LEAST(DATE '2025-01-01', DATE '2025-06-15', DATE '2025-03-20') AS v;  -- 2025-01-01

-- Realistic: cap a price at 100 (clamp to a maximum)
SELECT product_id, LEAST(list_price, 100.0) AS capped_price
FROM ecommerce.catalog.products;

-- Earliest deadline among several candidate dates
SELECT task_id,
       LEAST(due_date, sla_expires_at, contract_end_at) AS earliest_deadline
FROM projects.planning.tasks;

Pitfalls

NULL in any argument returns NULL. If you want to skip NULLs, wrap each argument with COALESCE to a sentinel value that will lose comparison.
String comparison follows the session collation. Use UPPER or LOWER for collation-independent results.
LEAST vs MIN: LEAST is horizontal (per row); MIN is vertical (across rows).
Mixing incompatible types raises a type error. Cast to a common type explicitly when mixing DATE and TIMESTAMP or INT and STRING.
Cross-engine NULL behavior differs. Assume propagation unless explicitly documented otherwise.