Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT MAX(price) AS most_expensive
FROM books;

Try it

SQL Largest values and the latest date ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Highest salary per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Who earns the maximum in each department? ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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 NULL returns NULL.
  • 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, use ORDER BY price DESC LIMIT 1 (or FETCH 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 BY you still get one row containing NULL; with GROUP BY an empty table yields no rows.
  • Performance. On an indexed column the maximum is read from the end of the index; no scan is needed.