MAX()

Syntax

How it works

MAX() scans the values in a group and returns the single largest one. It is one of the five core SQL aggregates alongside COUNT(), SUM(), AVG() and its mirror image MIN(), which returns the smallest value instead.

The comparison respects the column type. On numeric columns MAX gives you the highest number. On text columns it returns the value that sorts last under the collation, so Z beats A. On date and datetime columns it returns the latest point in time, which makes MAX(order_date) a clean way to find the most recent event.

Like every aggregate, NULLs are skipped: a NULL is never the maximum, and MAX over a group whose values are all NULL, or over no rows at all, returns NULL. Paired with GROUP BY it produces one maximum per group, and since SQL:2003 it can also run as a window function with OVER() to compute a running maximum while keeping every row.

Examples

Largest value in a column

SELECT MAX(total) AS biggest_order
FROM orders;

biggest_order
-------------
     4820.00

Maximum per group with GROUP BY

SELECT country, MAX(total) AS biggest_order
FROM orders
GROUP BY country
ORDER BY biggest_order DESC;

country | biggest_order
--------+--------------
 UAE    |       4820.00
 KSA    |       3110.50
 EGY    |       1975.00

Latest date (MAX works on dates)

-- most recent order per customer
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;

customer_id | last_order
------------+-----------
         1  | 2026-06-30
         2  | 2026-05-14

Running maximum as a window function

SELECT order_date, total,
       MAX(total) OVER (ORDER BY order_date) AS peak_so_far
FROM orders
ORDER BY order_date;

order_date | total | peak_so_far
-----------+-------+------------
 2026-01-02|  120  |         120
 2026-01-03|   80  |         120
 2026-01-05|  260  |         260

Fetch the whole row with the maximum (argmax)

-- the single most expensive order, all columns
SELECT id, customer_id, total, order_date
FROM orders
ORDER BY total DESC
LIMIT 1;

id | customer_id | total   | order_date
---+-------------+---------+-----------
 88|          17 | 4820.00 | 2026-03-11

Common mistakes

-- Trying to read another column alongside MAX
-- without grouping: this is invalid or picks a
-- random row on permissive engines
SELECT customer_id, MAX(total) FROM orders;

-- Want the row that HAS the maximum? Order and limit
SELECT customer_id, total
FROM orders
ORDER BY total DESC
LIMIT 1;

-- Aggregates are not allowed in WHERE
SELECT country, MAX(total)
FROM orders
WHERE MAX(total) > 1000
GROUP BY country;

SELECT country, MAX(total)
FROM orders
GROUP BY country
HAVING MAX(total) > 1000;

-- Expecting 0 for a table with no matching rows
SELECT MAX(total) FROM orders WHERE 1 = 0;  -- NULL

-- Supply a fallback when empty should read as 0
SELECT COALESCE(MAX(total), 0) FROM orders WHERE 1 = 0;

Performance

MAX() on an indexed column is one of the cheapest queries in SQL. Because a B-tree index stores values in sorted order, the optimiser can jump straight to the last entry instead of scanning the table. A plain SELECT MAX(price) FROM products with an index on price is effectively an index seek to one row.

That shortcut breaks when you add a per group MAX. SELECT category, MAX(price) ... GROUP BY category benefits from a composite index on (category, price), which lets the engine find the top price within each category without a full sort. See the indexing guide for how ordered index scans avoid sorting.

Fetching the full row with ORDER BY total DESC LIMIT 1 is also index friendly: an index on the ordering column lets the database read just the first row. Without an index it must sort the whole result, so index the columns you rank by.

Interview questions

-- top order per customer
SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY total DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

SELECT DISTINCT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;