GREATEST()

Syntax

How it works

GREATEST() takes two or more arguments and returns the largest one. The key point is that it works row by row, across the columns you list. For every row the database looks at the arguments side by side and returns whichever is biggest. It is a horizontal comparison, not a vertical one.

This is the opposite of MAX(). MAX() is an aggregate: it scans many rows and collapses a single column down to one value. GREATEST() keeps every row and simply picks the largest of several values within that row. If you have three score columns and want the best score per student, you want GREATEST(). If you have one score column and want the best score in the whole class, you want MAX(). Its mirror image is LEAST(), which returns the smallest of the arguments.

NULL handling is where the dialects diverge, so read this carefully. In MySQL, GREATEST() returns NULL if any argument is NULL, because NULL is treated as unknown and poisons the whole comparison. In PostgreSQL, GREATEST() ignores NULL arguments and returns the largest of the non-NULL values, only returning NULL when every argument is NULL. SQLite follows the PostgreSQL style and skips NULLs. SQL Server did not have the function at all until SQL Server 2022; on older versions you emulate it with a CASE expression or a VALUES subquery. See the SQL functions library for related helpers.

Examples

Highest of three score columns (row-wise)

-- One row per student, best of three attempts
SELECT student, GREATEST(score1, score2, score3) AS best_score
FROM exam_results;

student | best_score
--------+-----------
 Ali    |         88
 Sara   |         95
 Omar   |         72

Latest of several date columns

-- Most recent activity timestamp per user
SELECT user_id,
       GREATEST(last_login, last_order, last_comment) AS last_active
FROM user_activity;

user_id | last_active
--------+------------
     1  | 2026-06-30
     2  | 2026-07-01
     3  | 2026-05-14

Clamp a value to a floor of zero

-- Never let the adjusted balance go below 0
SELECT account_id,
       balance,
       GREATEST(balance - fee, 0) AS balance_after_fee
FROM accounts;

account_id | balance | balance_after_fee
-----------+---------+------------------
       10  |    50.0 |             45.0
       11  |     3.0 |              0.0
       12  |     0.0 |              0.0

Per-engine NULL behaviour

-- MySQL returns NULL because one argument is NULL.
-- PostgreSQL and SQLite return 10 (they ignore NULL).
SELECT GREATEST(10, NULL, 4) AS result;

MySQL       -> result = NULL
PostgreSQL  -> result = 10
SQLite      -> result = 10

Emulate GREATEST on older SQL Server

-- Pre-2022 SQL Server has no GREATEST; use VALUES
SELECT id,
       (SELECT MAX(v)
        FROM (VALUES (a), (b), (c)) AS t(v)) AS biggest
FROM measurements;

id | biggest
---+--------
 1 |      9
 2 |     14
 3 |      6

Common mistakes

-- WRONG: expecting the largest value in the column.
-- GREATEST compares its arguments row by row, so this
-- just returns the single column value on every row.
SELECT GREATEST(score) FROM exam_results;

-- To get the largest value down a column, use MAX().
SELECT MAX(score) AS top_score FROM exam_results;

-- On MySQL this returns NULL for every row that has
-- any NULL date, silently hiding real activity.
SELECT GREATEST(last_login, last_order) AS last_active
FROM user_activity;

-- Coalesce NULLs to a safe floor first on MySQL
SELECT GREATEST(
         COALESCE(last_login, '1970-01-01'),
         COALESCE(last_order, '1970-01-01')
       ) AS last_active
FROM user_activity;

-- Fails on SQL Server 2019 and earlier:
-- 'GREATEST is not a recognized built-in function name'
SELECT GREATEST(a, b, c) FROM measurements;

-- GREATEST was added in SQL Server 2022. On older
-- versions emulate it with CASE or a VALUES subquery.
SELECT (SELECT MAX(v)
        FROM (VALUES (a), (b), (c)) AS t(v)) AS biggest
FROM measurements;

Performance

GREATEST() is a lightweight scalar function evaluated once per row, so its own cost is negligible compared with reading the rows. The comparisons happen in memory and add almost nothing to a query that is already scanning the table.

The catch is that wrapping columns in GREATEST() inside a WHERE clause makes the predicate non sargable, so the optimiser cannot use a plain index on those columns and falls back to a full scan. If you filter on the result frequently, consider a computed or generated column that stores GREATEST(...) and index that instead.

Do not confuse the row-wise cost of GREATEST() with the aggregate cost of MAX(). MAX() may need to sort or scan an index across many rows, whereas GREATEST() never looks beyond the current row.

Interview questions

-- Best of three columns per row vs largest in a column
SELECT GREATEST(a, b, c) AS row_best FROM t;
SELECT MAX(a)            AS col_max  FROM t;

SELECT GREATEST(balance - fee, 0) AS floored FROM accounts;