MySQL max() Function

Last updated: October 19, 2026Oct 19, 2026 4 min read

MAX() returns the largest value in a set: the longest run, the highest price, the most recent date. On its own it's simple. The interesting part is answering the follow-up question: which row had that maximum?

1. Basic MAX

Table: runs (8 rows)

SELECT MAX(km) AS longest_run, MAX(minutes) AS slowest_time, MAX(run_date) AS latest_run FROM runs;

Result: 1 row

MAX ignores NULLs (Manav's missing time). For text it returns the value that sorts last in the collation:

SELECT MAX(athlete) AS last_alphabetically, MAX(city) AS city FROM runs;

Result: 1 row

2. MAX per Group

SELECT athlete, MAX(km) AS best_km, MAX(run_date) AS last_run FROM runs GROUP BY athlete ORDER BY best_km DESC;

Result: 4 rows

SELECT city, MAX(km) AS longest FROM runs GROUP BY city HAVING MAX(km) >= 20;

Result: 3 rows

3. Getting the Whole Row with the Maximum

A common mistake is selecting other columns next to MAX and expecting them to come from the same row. With ONLY_FULL_GROUP_BY MySQL refuses:

SELECT athlete, MAX(km), run_date FROM runs GROUP BY athlete;

ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fitness.runs.run_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Method 1: correlated sub-query.

SELECT athlete, km, run_date
FROM runs r
WHERE km = (SELECT MAX(km) FROM runs WHERE athlete = r.athlete)
ORDER BY athlete;

Result: 4 rows

Method 2: window function (MySQL 8+), which also lets you choose how to break ties:

SELECT athlete, km, run_date
FROM (
  SELECT r.*, ROW_NUMBER() OVER (PARTITION BY athlete ORDER BY km DESC, run_date) AS rn
  FROM runs r
) ranked
WHERE rn = 1
ORDER BY athlete;

Result: 4 rows

For the single overall maximum, ORDER BY + LIMIT is simplest:

SELECT athlete, km, run_date FROM runs ORDER BY km DESC, run_date LIMIT 1;

Result: 1 row

4. Ties

Lata and Nora both ran 42.2 km. The sub-query method returns every tied row; LIMIT 1 and ROW_NUMBER pick one:

SELECT athlete, km FROM runs WHERE km = (SELECT MAX(km) FROM runs);

Result: 2 rows

5. MAX of an Expression

SELECT athlete, ROUND(MAX(km / minutes * 60), 2) AS best_speed_kmh FROM runs GROUP BY athlete ORDER BY best_speed_kmh DESC;

Result: 4 rows

6. MAX vs GREATEST

MAX() compares values across rows; GREATEST() compares values across columns in one row:

SELECT GREATEST(4, 19, 7) AS greatest, GREATEST(10, NULL) AS with_null;

Result: 1 row

7. MAX and Indexes

With an index on the column, MySQL reads the maximum straight from the end of the index (Select tables optimized away):

CREATE INDEX idx_km ON runs (km);
EXPLAIN SELECT MAX(km) FROM runs;

Result: 1 row

8. Practice Exercises

SELECT city, MAX(run_date) AS latest FROM runs GROUP BY city ORDER BY latest DESC;

SELECT athlete, km, city FROM (
  SELECT athlete, km, city, ROW_NUMBER() OVER (PARTITION BY athlete ORDER BY km DESC) AS rn FROM runs
) t WHERE rn = 1;

9. Summary

• MAX(col) returns the largest non-NULL value: numbers, dates or text.
• Use GROUP BY for a maximum per group.
• To get the whole row, use a correlated sub-query, ROW_NUMBER()/RANK(), or ORDER BY … LIMIT 1.
• GREATEST compares columns within a row; an index makes MAX instant.