SQL Reference ยท Aggregate Functions
SQL MAX() function
Reference for the SQL MAX() aggregate: largest number, latest date or last text value, NULL handling, per-group maximums and the row that holds it.
MAX(expr) is an aggregate function that returns the largest non-NULL value of expr in a group of rows. It is the mirror image of MIN() and works on numbers, dates (latest) and text (last in the collation order).
You use it for the highest salary, the most recent order date, the newest version number. It returns the value only; to see the rest of the row, use a subquery or a window function, as shown below.
SELECT MAX(price) AS most_expensive FROM books;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
MAX(expr) MAX(expr) OVER (...) -- as a window function
| Part | Description |
|---|---|
expr |
A column or expression of a comparable type. NULLs are ignored. |
Returns: the largest non-NULL value with the same type as expr, or NULL if there are no non-NULL values.
Dialect differences
| Database | Notes |
|---|---|
| SQLite | With two or more arguments, MAX(a, b, c) is a scalar function returning the largest argument (NULL if any is NULL). With one argument it is the aggregate. |
| PostgreSQL | Aggregate only; use GREATEST(a, b, c) for several values (it ignores NULLs). |
| MySQL | Aggregate only; GREATEST() returns NULL if any argument is NULL. |
| SQL Server | Aggregate only; GREATEST() exists from SQL Server 2022. |
| Oracle | Aggregate only; GREATEST() returns NULL if any argument is NULL. |
| Booleans | PostgreSQL has no MAX(boolean); use BOOL_OR. SQL Server has no boolean type to aggregate. |
-- PostgreSQL: largest of two columns in each row SELECT GREATEST(salary, 50000) FROM employees;
Usage notes
- NULLs are skipped. A group whose values are all
NULLreturnsNULL. - Latest date.
MAX(ordered_on)works if dates are stored in a sortable form such as ISO 8601 text or a real date type. - Text is compared by collation.
MAX(name)is the alphabetically last name, not the longest. - Getting the row.
SELECT title, MAX(price)is not valid standard SQL. Compare to a subquery, useORDER BY price DESC LIMIT 1(orFETCH FIRST 1 ROW ONLY), or rank rows with window functions. - Latest row per group is a classic task: join to a subquery of per-group maximums, or use
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC). - Empty input. Without
GROUP BYyou still get one row containingNULL; withGROUP BYan empty table yields no rows. - Performance. On an indexed column the maximum is read from the end of the index; no scan is needed.
Related
- MIN(), SUM(), AVG(), GROUP BY, subqueries
- Lessons: Aggregate functions, Correlated subqueries