GREATEST and LEAST

GREATEST and LEAST return the largest or smallest value, respectively, among a list of expressions passed as arguments.

Overview

GREATEST(expr1, expr2, ..., exprN) returns the largest value among its arguments; LEAST(...) returns the smallest. Unlike MAX/MIN, which aggregate a column down a set of rows, GREATEST/LEAST operate row-wise across a fixed list of expressions or columns on the same row.

Copy code
SELECT product_id,
       GREATEST(price_2024, price_2025, price_2026) AS highest_price,
       LEAST(price_2024, price_2025, price_2026)    AS lowest_price
FROM product_pricing;

Common use case: clamping values

A frequent pattern combines both functions to clamp a value within a range:

Copy code
SELECT GREATEST(0, LEAST(100, raw_score)) AS clamped_score
FROM assessments;
-- ensures the result is never below 0 or above 100

NULL handling

Behavior around NULL arguments varies subtly by database and by data type. In DuckDB, GREATEST/LEAST generally ignore NULL arguments and return the extreme value among the remaining non-NULL arguments, returning NULL only if every argument is NULL. This differs from a naive expectation that any NULL argument should "poison" the result, so it's worth testing behavior explicitly if NULLs are common in the input and correctness is critical.

GREATEST/LEAST vs MAX/MIN

GREATEST/LEAST compare a fixed, finite list of expressions across a single row.
MAX/MIN are aggregate functions that reduce many rows (optionally within a GROUP BY group) down to one value.

They're often combined -- for example, using MAX inside a GROUP BY to find a per-group maximum, and GREATEST to compare that result against a fixed threshold on each row.

Type coercion

All arguments should share a common comparable type; DuckDB will attempt to find a common supertype (e.g., mixing INTEGER and DOUBLE arguments) before comparing.

Related terms

COALESCE →

COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.

NULL →

NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.

NULLIF →

NULLIF returns NULL if two expressions are equal, and otherwise returns the first expression -- commonly used to avoid divide-by-zero errors or convert sentinel values into NULL.

EXISTS →

EXISTS tests whether a subquery returns at least one row, commonly used to check for the presence of related records without caring about their actual values.

aggregate functions →

Aggregate functions are SQL operations that perform calculations across multiple rows of data to produce a single summary value.

Data profiling →

Data profiling is the process of examining a dataset to understand its structure, content, and quality — things like data types, value distributions, null rates, and cardinality — before using it for analysis or building pipelines on top of it.

FAQS

GREATEST compares a fixed list of expressions within a single row, while MAX is an aggregate function that finds the maximum value across many rows.

DuckDB generally ignores NULL arguments and returns the extreme value among the remaining ones, returning NULL only when every argument is NULL -- behavior here can have edge cases with certain numeric types, so test it if NULL handling is critical.

Yes -- GREATEST(min_bound, LEAST(max_bound, value)) is a common idiom to constrain a value between a minimum and maximum.