GREATEST

Return the greatest value from a list of expressions.

Category: conditionalReturns: ANYDialect: Standard

Syntax

GREATEST(expr1, expr2, ...)

Description

## Overview Returns the greatest value among its arguments according to the ordering rules of the resolved common supertype. GREATEST accepts two or more arguments and works on any orderable type: numbers, strings, dates, timestamps, and engine-supported composite types. Use GREATEST to compute maxima across columns within a single row (per-row max), to clamp a value to a minimum floor, or to pick the most recent timestamp across multiple candidate columns. ## Behavior - Evaluates all arguments (non short-circuit). - If any argument is NULL, GREATEST 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, so referencing the same expression twice is cheap. ## Compatibility - GREATEST is part of ANSI SQL:2003. Engines differ on NULL handling: some propagate (NULL anywhere returns NULL), others skip NULLs and return the maximum of the non-NULL arguments. DeltaForge propagates NULL. When porting queries from engines with skip-NULL semantics, wrap arguments with COALESCE to a sentinel that participates in the desired 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 GREATEST(10, 20, 30) AS v;  -- 30

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

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

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

-- Realistic: floor a price to 0 (clamp to non-negative)
SELECT product_id, GREATEST(list_price - discount, 0.0) AS effective_price
FROM ecommerce.catalog.products;

-- Most recent activity timestamp across multiple sources
SELECT user_id,
       GREATEST(last_login_at, last_page_view_at, last_purchase_at) AS last_active_at
FROM analytics.telemetry.user_activity;

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, which may be case-insensitive or accent-insensitive. Cast to a binary form (UPPER or LOWER) for collation-independent results.
Numeric widening: mixing INT and DECIMAL promotes the result to DECIMAL; mixing numeric and string raises a type error.
GREATEST vs MAX: GREATEST is horizontal (per row, across arguments); MAX is vertical (across rows). They are not interchangeable.
Cross-engine NULL behavior differs. DeltaForge propagates NULL; some other engines skip NULL arguments. Assume propagation unless explicitly documented otherwise.