SQL GREATEST

What is SQL GREATEST?

Returns the largest (greatest) non-NULL value from a list of expressions evaluated row by row.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL GREATEST: PostgreSQL, MySQL 8.0+, Oracle, Snowflake, BigQuery. Not available in SQL Server or SQLite (use CASE or custom logic instead).

SQL GREATEST Full Explanation

GREATEST is a scalar comparison function that evaluates two or more expressions for every row and returns the highest non-NULL value according to the database’s sorting rules. All arguments are implicitly converted to a common data type if necessary. If every argument is NULL the function returns NULL. When text values are supplied, the comparison follows the database’s collation rules; when numbers are supplied, standard numeric comparison is used. Mixing incomparable data types (e.g., text with date) raises an error. Because evaluation is row-wise, GREATEST can be used both in SELECT lists and in WHERE or ORDER BY clauses. It does not aggregate across rows; use MAX for that purpose.

SQL GREATEST Syntax

GREATEST ( expression1 [, expression2 [, ... expressionN ] ] );

SQL GREATEST Parameters

expression1, expression2, … expressionN (Any comparable scalar type) - Two or more expressions whose greatest value you want returned

Example Queries Using SQL GREATEST

-- Find the highest test score per student
SELECT student_id,
       GREATEST(math_score, science_score, english_score) AS top_score
FROM   exam_results;

-- Use with dates to get the most recent activity per row
SELECT user_id,
       GREATEST(last_login, last_purchase, last_support_ticket) AS last_activity
FROM   users;

-- Handle NULLs (returns NULL if all are NULL)
SELECT order_id,
       GREATEST(shipped_at, delivered_at, returned_at) AS last_status_change
FROM   orders;

Expected Output Using SQL GREATEST

Each query returns one additional column containing the greatest value among the supplied expressions for that row
NULL is returned if all inputs are NULL

Use Cases with SQL GREATEST

Select the most recent timestamp among several date columns
Determine the highest of multiple numeric metrics stored in different columns
Simplify CASE expressions that pick the maximum of several possibilities
Combine with COALESCE to fall back to a default when all inputs are NULL

Common Mistakes with SQL GREATEST

Assuming GREATEST aggregates across rows; it only compares values in the same row
Forgetting that if every argument is NULL the result is NULL, which may cause filtering errors
Mixing non-comparable data types, leading to type-mismatch errors
Expecting support in SQL Server or older MySQL versions (before 8.0)

First Introduced In

Oracle 7 (1992); later adopted by PostgreSQL and others

Frequently Asked Questions

What is the difference between GREATEST and MAX?

GREATEST compares values within the same row, while MAX aggregates values across multiple rows in a result set.

How do I use GREATEST with NULL values?

GREATEST ignores NULLs as long as at least one argument has a value. If all arguments are NULL, it returns NULL. Wrap it in COALESCE if you need a fallback value.

Can I use GREATEST in a WHERE clause?

Yes. Because it is a scalar function, you can use it anywhere an expression is allowed, including SELECT, WHERE, ORDER BY, and GROUP BY clauses.

What are alternatives in SQL Server or SQLite?

Use a CASE expression like CASE WHEN a > b AND a > c THEN a WHEN b > c THEN b ELSE c END, or CROSS APPLY with VALUES to emulate GREATEST.