---
title: "GREATEST and LEAST"
description: "GREATEST and LEAST return the largest or smallest value, respectively, among a list of expressions passed as arguments."
canonical: "https://motherduck.com/glossary/greatest-and-least/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
  - title: "Beyond the Benchmarks: A BigQuery Co-Founder's Guide to Evaluating Data Warehouse Performance | MotherDuck"
    url: "https://motherduck.com/videos/lies-damn-lies-and-benchmarks/"
gated_asset:
  title: "AI Analytics Eval Field Guide"
  url: "https://motherduck.com/lp/ai-analytics-eval-field-guide-full/"
---

# 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.

```sql
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:

```sql
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 `NULL`s 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.