GREATEST / LEASTFunctions
Buy Me A Coffee

Return the largest or smallest value from a list of two or more expressions. Evaluate multiple columns or values in a single call without nested CASE expressions.

Return the greatest or least value from a list of arguments. Useful for calculating caps and floors in application logic. In some databases (MySQL), NULL arguments cause the whole result to be NULL -- verify behavior or wrap in COALESCE.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 3.23 GREATEST(v1, v2, ...) and LEAST(v1, v2, ...) support any number of arguments. If any argument is NULL, the result is NULL. Arguments are compared after implicit type conversion — mixing types triggers coercion rules. Works with numbers, strings, and dates.
PostgreSQL ✓ Supported 6.0 GREATEST and LEAST support any number of arguments and any comparable type. Unlike most SQL functions, PostgreSQL's GREATEST/LEAST ignore NULLs and return a non-NULL result if at least one argument is non-NULL. This differs from MySQL and Oracle, where any NULL input produces a NULL result.
SQL Server ✓ Supported 2022 GREATEST() and LEAST() were added in SQL Server 2022. Unlike MySQL/Oracle, SQL Server's implementation ignores NULLs (matching PostgreSQL behavior). For SQL Server 2019 and earlier, use a VALUES subquery with aggregation or nested IIF/CASE expressions as a workaround.
Oracle ✓ Supported 7 GREATEST and LEAST supported since Oracle 7. If any argument is NULL, the result is NULL (same as MySQL, opposite of PostgreSQL). Arguments must be compatible types. Works with NUMBER, VARCHAR2, DATE, and TIMESTAMP.
SQLite ✗ Not Supported — SQLite has no GREATEST or LEAST functions. Workaround for two values: use a CASE expression. For more values, use a subquery with MAX/MIN over a VALUES list (requires SQLite 3.8.3+ for VALUES in subqueries).

Details

The critical portability trap is NULL handling: MySQL and Oracle return NULL if any argument is NULL; PostgreSQL and SQL Server 2022 ignore NULLs and return the greatest/least non-NULL value. When porting between engines, wrap arguments in COALESCE/NVL/ISNULL to normalize NULL behavior. SQL Server only gained GREATEST/LEAST in 2022 — the VALUES subquery pattern (SELECT MAX(v) FROM (VALUES (a),(b),(c)) t(v)) is the cleanest pre-2022 workaround and is also the correct SQLite workaround. SQLite is the only engine in the compatibility matrix with no support.

Standard Syntax

-- Clamp a value between a min and max: SELECT GREATEST(LEAST(score, 100), 0) AS clamped_score FROM results; -- Latest of two dates: SELECT GREATEST(start_date, effective_date) AS use_date FROM contracts;

Version Support

MySQL: Since 3.23 PostgreSQL: Since 6.0 SQL Server: Since 2022 Oracle: Since 7 SQLite: Not supported

Per-Database Syntax & Notes

MySQL

GREATEST(v1, v2, ...) and LEAST(v1, v2, ...) support any number of arguments. If any argument is NULL, the result is NULL. Arguments are compared after implicit type conversion — mixing types triggers coercion rules. Works with numbers, strings, and dates.

-- Largest of two columns: SELECT id, GREATEST(q1_sales, q2_sales, q3_sales, q4_sales) AS peak_quarter FROM yearly_sales; -- Clamp between bounds: SELECT GREATEST(LEAST(user_rating, 5), 1) AS clamped_rating FROM reviews; -- Latest date: SELECT GREATEST(created_at, updated_at) AS last_touched FROM records; -- NULL propagation: SELECT GREATEST(1, NULL, 3); -- Returns NULL

PostgreSQL

GREATEST and LEAST support any number of arguments and any comparable type. Unlike most SQL functions, PostgreSQL's GREATEST/LEAST ignore NULLs and return a non-NULL result if at least one argument is non-NULL. This differs from MySQL and Oracle, where any NULL input produces a NULL result.

-- Greatest of multiple values: SELECT GREATEST(q1, q2, q3, q4) AS peak FROM quarterly_results; -- NULL handling (PostgreSQL ignores NULLs): SELECT GREATEST(1, NULL, 3); -- Returns 3 (not NULL) SELECT LEAST(1, NULL, 3); -- Returns 1 (not NULL) -- Clamp: SELECT GREATEST(LEAST(score, 100), 0) AS score_0_to_100 FROM test_results; -- Date comparison: SELECT GREATEST(signup_date, activation_date) AS first_activity FROM users;

SQL Server

GREATEST() and LEAST() were added in SQL Server 2022. Unlike MySQL/Oracle, SQL Server's implementation ignores NULLs (matching PostgreSQL behavior). For SQL Server 2019 and earlier, use a VALUES subquery with aggregation or nested IIF/CASE expressions as a workaround.

-- SQL Server 2022+: SELECT GREATEST(q1_sales, q2_sales, q3_sales) AS peak_quarter FROM quarterly_results; SELECT LEAST(ship_date, delivery_date) AS earlier_date FROM shipments; -- Workaround for SQL Server 2019 and earlier (2 values): SELECT IIF(q1 > q2, q1, q2) AS greatest_2 FROM quarterly_results; -- Workaround for SQL Server 2019 and earlier (3+ values via VALUES): SELECT (SELECT MAX(v) FROM (VALUES (q1), (q2), (q3)) AS t(v)) AS peak FROM quarterly_results;

Oracle

GREATEST and LEAST supported since Oracle 7. If any argument is NULL, the result is NULL (same as MySQL, opposite of PostgreSQL). Arguments must be compatible types. Works with NUMBER, VARCHAR2, DATE, and TIMESTAMP.

SELECT GREATEST(q1_sales, q2_sales, q3_sales, q4_sales) AS peak_quarter FROM quarterly_results; -- Clamp: SELECT GREATEST(LEAST(score, 100), 0) AS clamped_score FROM exam_results; -- NULL propagation: SELECT GREATEST(1, NULL, 3) FROM dual; -- Returns NULL -- NVL workaround when NULLs must be handled: SELECT GREATEST(NVL(col1, 0), NVL(col2, 0)) AS safe_greatest FROM t;

SQLite

SQLite has no GREATEST or LEAST functions. Workaround for two values: use a CASE expression. For more values, use a subquery with MAX/MIN over a VALUES list (requires SQLite 3.8.3+ for VALUES in subqueries).

-- CASE workaround (2 values): SELECT CASE WHEN a >= b THEN a ELSE b END AS greatest_of_two FROM t; -- VALUES subquery workaround (3+ values, SQLite 3.8.3+): SELECT (SELECT MAX(v) FROM (VALUES (col1), (col2), (col3)) AS t(v)) AS greatest_of_three FROM my_table;