GREATEST and LEAST in SQL: Row-Wise Max and Min Across Columns

GREATEST and LEAST take a list of arguments and return the largest or the smallest of them within a single row. You reach for them when you need to compare not rows against each other, but several columns or expressions inside the current row: picking the more recent of two dates, putting a floor or a ceiling on a number, or pinning a value into a range. They are not aggregate functions: instead of collapsing rows into groups, they look at columns side by side, horizontally, and emit one value per row.

Both functions share the same signature: the input is a list of two or more expressions of comparable type, and the output is a single value of that same type. GREATEST returns the maximum of the list, LEAST the minimum. You can mix the arguments freely (columns, literals, the results of other calls), which is why the two functions nest so easily inside one another.

Two things deserve a check up front: types and NULL. The types must be comparable, or PostgreSQL coerces them to a common type or refuses to run the query; and a NULL in the argument list is handled differently by each database, which is exactly where a ported query tends to break.

How this differs from MAX and MIN

The confusion comes from the words: a "maximum" is a "maximum." But the two families work in different directions. MAX and MIN are aggregates: they run vertically, down the rows of a group, and return one number for the whole result set. GREATEST and LEAST run horizontally, across the arguments of one row, and return a value for every row.

-- Aggregate: one number per group, scans many rows
SELECT MAX(amount) AS biggest_order
FROM orders;

-- Row-wise: one value per row, compares columns side by side
SELECT id, GREATEST(amount, 10) AS amount_floor_10
FROM orders;

A classic case is picking the most recent of several dates right in the row, without subqueries or window functions:

SELECT
    u.id,
    GREATEST(u.created_at, o.created_at) AS last_touch
FROM users u
JOIN orders o ON o.user_id = u.id;

Clamping a value to a range

The most practical trick with these functions is to drive a number into a corridor between a lower and an upper bound. You do it by nesting one call inside the other: GREATEST(lo, LEAST(hi, x)) guarantees the result never escapes [lo, hi]. Read the formula from the inside out: first trim from the top, then lift from the bottom.

-- Clamp the order amount into the range [1, 1000]
SELECT
    id,
    amount,
    GREATEST(1, LEAST(1000, amount)) AS amount_clamped
FROM orders;

It is indispensable wherever a value must stay within bounds: discounts, loyalty points, normalizing salaries for reporting.

-- Cap every salary at 200000 but never below 30000
SELECT
    id,
    name,
    GREATEST(30000, LEAST(200000, salary)) AS salary_banded
FROM employees;

Here is who does what:

• the outer GREATEST(lo, ...) lifts values that are too small up to lo;
• the inner LEAST(hi, x) pulls values that are too large down to hi;
• the order of the bounds is critical: if you accidentally set lo > hi, the corridor collapses into a contradiction and you get nonsense out, silently, with no error.

NULL handling: the main landmine

This is where it gets treacherous. NULL behavior diverges between engines, and that is exactly what breaks code when you move from one database to another.

• PostgreSQL: NULL arguments are simply dropped from the comparison. GREATEST(5, NULL, 9) returns 9. Only when every single argument is NULL does the result become NULL too.
• MySQL: a single NULL among the arguments is enough to zero out the whole result. GREATEST(5, NULL, 9) returns NULL in MySQL — one NULL poisons the entire expression.
• ClickHouse: semantically closer to MySQL. If even one Nullable argument is NULL, the result is NULL too. You cannot rely on PostgreSQL-style NULL skipping here, so do not file ClickHouse away as a copy of PostgreSQL; guard your arguments explicitly instead.

-- PostgreSQL: returns 9, NULL is ignored
-- MySQL and ClickHouse: return NULL, one NULL poisons the result
SELECT GREATEST(5, NULL, 9) AS demo;

Gotcha: do not count on "the NULL will drop itself" when porting a query from PostgreSQL to MySQL or ClickHouse. Logic that quietly worked for years can suddenly start returning NULL after the migration, and spotting it by eye in a large query is nearly impossible. If a column can be NULL, wrap it in COALESCE so the behavior is explicit and identical everywhere.

-- Portable: define a neutral fallback before comparing
SELECT
    id,
    GREATEST(COALESCE(amount, 0), 0) AS amount_nonneg
FROM orders;

Useful combinations

GREATEST and LEAST fit well beyond SELECT. Inside an UPDATE and in computed expressions they spare you from bulky CASE blocks.

-- Bump salary by 10% but never below the floor of 40000
UPDATE employees
SET salary = GREATEST(40000, salary * 1.10)
WHERE dept = 'sales';

-- Days since the more recent of signup or first order
SELECT
    u.id,
    CURRENT_DATE - GREATEST(u.created_at, o.created_at)::date AS days_idle
FROM users u
JOIN orders o ON o.user_id = u.id;

To sum up: GREATEST and LEAST compare values horizontally, within a row; they clamp a number neatly into a range through a nested call; and they demand explicit COALESCE protection if the same code must behave the same on PostgreSQL, MySQL, and ClickHouse. Learn the NULL difference once and you will save yourself an evening of debugging later.