MAX Function
SQL · Aggregate Functions
Theory
MAX returns the largest value in a column across a set of rows. It works on any type that can be sorted: for numbers it is the biggest number, for dates it is the latest date, and for text it is the value that sorts last alphabetically (so MAX(name) of Asha, Ravi and Meera is Ravi). NULLs are ignored, and MAX returns NULL only when there are no non-NULL values.
With GROUP BY you get the maximum per group, for example each customer's largest order and latest order date. One common mistake: MAX gives you the value, not the row. SELECT customer_id, MAX(amount), order_id is not valid in most databases, because order_id is not tied to the max. To get the whole row behind the maximum, use a subquery, a join back, or ROW_NUMBER().
Why we use it
'What is the highest?' and 'when was the latest?' are everyday questions: top salary, biggest order, most recent login. MAX answers them in one pass without sorting the whole table yourself.
When to use it
Use MAX for the largest number or the latest date or timestamp, per group with GROUP BY. If you need the full row that holds the max (who earns it, which order), use ROW_NUMBER() or a join to the max. For text, remember it is alphabetical, not by length.
How it works
- Rows arrive after FROM and WHERE
- GROUP BY splits rows into groups
- Start with no current maximum
- Is the next value NULL? Yes: Skip it. No: Keep it if it is larger than the current maximum.
- Repeat for every row in the group
- Return the maximum (NULL if no values)
Syntax
MAX(column)Example
SELECT MAX(salary) FROM employees;Key points
- Works on numbers, dates and text (text = last alphabetically)
- Ignores NULLs; returns NULL only if no values exist
- Often paired with GROUP BY for per-group maximums
- Gives the value, not the row: use ROW_NUMBER() to fetch the row
- Store dates as real DATE types, or as YYYY-MM-DD text, so MAX sorts correctly