aggregate
MAX
Returns the largest non-NULL value in a group by the column's natural ordering. Useful for latest timestamps, top prices, or alphabetically last strings.
MAX(expression)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| OVER (...) | Windowed maximum across a partition |
| FILTER (WHERE ...) | Postgres: conditional maximum |
| DISTINCT | Accepted but redundant for MAX (kept for grammar symmetry) |
Examples
SELECT MAX(paid_at) FROM invoices WHERE user_id = 7;Latest payment timestamp for user 7
SELECT category, MAX(price) FROM products GROUP BY category;Most expensive item per category
SELECT MAX(login_at) - MIN(login_at) FROM sessions;Time span between first and last recorded login
SELECT MAX(CASE WHEN plan = 'pro' THEN 1 ELSE 0 END) FROM subs;Portable 'has any pro row' flag using MAX over 0/1
Dialect notes / Gotcha
MAX over dates works but MAX(interval) is Postgres-only. For 'top-N' rows, ROW_NUMBER/RANK is usually cleaner than nested MAX subqueries.
Related functions
MIN
Returns the smallest non-NULL value in a group, using natural ordering for the column's data type. Works for numbers, dates, and strings (lexicographic).
GREATEST / LEAST
Return the largest (GREATEST) or smallest (LEAST) of the provided arguments row-by-row — not across rows, which is MIN/MAX. NULL handling differs by engine: Postgres ignores NULL args, MySQL returns NULL if any argument is NULL.
ROW_NUMBER / RANK
Window functions that assign an ordinal within a partition. ROW_NUMBER gives every row a distinct sequential integer; RANK and DENSE_RANK repeat values on ties (RANK leaves gaps, DENSE_RANK does not).
COUNT
Returns the number of rows matching a query, optionally counting non-NULL values of an expression or distinct values. COUNT(*) counts every row including NULLs, while COUNT(expr) skips NULLs.
SUM
Adds up all non-NULL numeric values in a column or expression. Returns NULL (not 0) if every input row is NULL or the result set is empty.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet