MySQL Select Row with MAX Value for Each Group

MySQL Select Row with MAX Value for Each Group, when working with grouped data in MySQL, a common requirement is to find the row containing the maximum value within each group.

For example, suppose you have basketball statistics and want to find the player with the highest number of points for every team. A simple MAX() aggregation can tell you the highest score, but it does not automatically return the other columns from the corresponding row.

One way to solve this problem is to use a correlated subquery:

SELECT*FROM athletes a1WHERE points = (SELECT MAX(a2.points)FROM athletes a2WHERE a1.team = a2.team);

This query compares each player’s points value with the maximum points scored by players on the same team.

The result is one or more rows containing the maximum points value for each team.

Example: Select Row with MAX Value for Each Group in MySQL

Suppose we have a table named athletes containing information about basketball players:

-- create tableCREATETABLE athletes (    id INTPRIMARYKEY,    team TEXT NOTNULL,    points INTNOTNULL);

We can insert some sample data:

INSERTINTO athletes VALUES(1, 'Mavs', 22),(2, 'Mavs', 14),(3, 'Lakers', 37),(4, 'Knicks', 19),(5, 'Knicks', 26),(6, 'Knicks', 40),(7, 'Lakers', 21),(8, 'Celtics', 15),(9, 'Hawks', 18),(10, 'Celtics', 23);

We can view the complete table using:

SELECT*FROM athletes;

The output is:

+----+---------+--------+| id | team    | points |+----+---------+--------+|  1 | Mavs    |     22 ||  2 | Mavs    |     14 ||  3 | Lakers  |     37 ||  4 | Knicks  |     19 ||  5 | Knicks  |     26 ||  6 | Knicks  |     40 ||  7 | Lakers  |     21 ||  8 | Celtics |     15 ||  9 | Hawks   |     18 || 10 | Celtics |     23 |+----+---------+--------+

Suppose we want to find the entire row with the highest points value for each team.

We can use the following query:

SELECT*FROM athletes a1WHERE points = (SELECT MAX(a2.points)FROM athletes a2WHERE a1.team = a2.team);

The result is:

+----+---------+--------+| id | team    | points |+----+---------+--------+|  1 | Mavs    |     22 ||  3 | Lakers  |     37 ||  6 | Knicks  |     40 ||  9 | Hawks   |     18 || 10 | Celtics |     23 |+----+---------+--------+

The query returns the row containing the highest score for every team.

For example, the Knicks team has three rows:

192640

The maximum is 40, so only the row with id = 6 is returned for the Knicks.

Similarly, the Lakers have scores of 37 and 21, so only the row with 37 is returned.

How the Correlated Subquery Works

The query contains two references to the athletes table:

athletes a1

and:

athletes a2

The outer query uses a1:

SELECT*FROM athletes a1

The inner query uses a2:

SELECT MAX(a2.points)FROM athletes a2WHERE a1.team = a2.team

For each row in the outer query, the inner query finds the maximum points value for that player’s team.

For example, when MySQL evaluates a row where:

team = 'Knicks'points = 26

the subquery calculates:

MAX(points) for Knicks = 40

MySQL then evaluates:

26 = 40

which is false, so that row is excluded.

For the Knicks row with:

points = 40

MySQL evaluates:

40 = 40

which is true, so the row is returned.

Why MAX() Alone Is Not Enough

You might initially try:

SELECT team, MAX(points)FROM athletesGROUPBY team;

This returns:

+---------+-------------+| team    | MAX(points) |+---------+-------------+| Celtics |          23 || Hawks   |          18 || Knicks  |          40 || Lakers  |          37 || Mavs    |          22 |+---------+-------------+

This correctly identifies the maximum score for each team.

However, it does not return the id or other columns from the corresponding row.

If your table contained additional information such as:

idteamplayer_namepositionpointssalary

you might want all of those values for the player with the highest score.

The correlated subquery solves this problem because it returns the complete row.

Select Specific Columns Instead of SELECT *

It is usually better to explicitly specify the columns you need.

For example:

SELECT    a1.id,    a1.team,    a1.pointsFROM athletes a1WHERE a1.points = (SELECT MAX(a2.points)FROM athletes a2WHERE a1.team = a2.team);

This makes the query easier to understand and avoids unnecessarily returning columns that you do not need.

Using a JOIN Instead of a Correlated Subquery

Another common solution is to calculate the maximum value for each team and then join that result back to the original table.

SELECT    a.id,    a.team,    a.pointsFROM athletes aJOIN (SELECT        team,        MAX(points) AS max_pointsFROM athletesGROUPBY team) mON a.team = m.teamAND a.points = m.max_points;

The inner query calculates the maximum points for each team:

SELECT    team,    MAX(points) AS max_pointsFROM athletesGROUPBY team;

Then the result is joined back to athletes.

This approach is particularly useful when working with more complicated queries or when you want to separate the aggregation step from the row-selection step.

Using ROW_NUMBER() in MySQL 8.0+

If you are using MySQL 8.0 or later, a window function provides another elegant solution.

You can use ROW_NUMBER():

SELECT    id,    team,    pointsFROM (SELECT        id,        team,        points,        ROW_NUMBER() OVER (            PARTITION BY teamORDERBY points DESC        ) AS rnFROM athletes) rankedWHERE rn =1;

The important part is:

ROW_NUMBER() OVER (    PARTITION BY teamORDERBY points DESC)

PARTITION BY team creates a separate ranking for each team.

ORDER BY points DESC places the highest-scoring player first.

Therefore:

rn =1

represents the highest-scoring row within each team.

MAX() vs ROW_NUMBER()

There is an important difference between the correlated-subquery approach and ROW_NUMBER().

Suppose two players on the same team both have the maximum score:

id | team  | points---+-------+-------1  | Mavs  | 302  | Mavs  | 303  | Mavs  | 20

The correlated subquery:

SELECT*FROM athletes a1WHERE points = (SELECT MAX(a2.points)FROM athletes a2WHERE a1.team = a2.team);

returns both players with 30 points.

That may be exactly what you want if ties should be preserved.

However, ROW_NUMBER() returns only one row:

ROW_NUMBER() OVER (    PARTITION BY teamORDERBY points DESC)

If you want to return all tied rows using a window function, use RANK() instead:

SELECT    id,    team,    pointsFROM (SELECT        id,        team,        points,        RANK() OVER (            PARTITION BY teamORDERBY points DESC        ) AS rnkFROM athletes) rankedWHERE rnk =1;

This returns every row tied for the maximum score.

Using RANK() to Keep Ties

Consider this data:

+----+---------+--------+| id | team    | points |+----+---------+--------+|  1 | Mavs    |     30 ||  2 | Mavs    |     30 ||  3 | Mavs    |     20 ||  4 | Lakers  |     35 ||  5 | Lakers  |     25 |+----+---------+--------+

Using:

RANK() OVER (    PARTITION BY teamORDERBY points DESC)

both Mavs players with 30 points receive rank 1.

Therefore:

WHERE rnk =1

returns both rows.

This makes RANK() useful when tied maximum values need to be retained.

Using ROW_NUMBER() to Select Exactly One Row

Sometimes you don’t want every tied row. You need exactly one row per group.

For example:

SELECT    id,    team,    pointsFROM (SELECT        id,        team,        points,        ROW_NUMBER() OVER (            PARTITION BY teamORDERBY points DESC, id ASC        ) AS rnFROM athletes) rankedWHERE rn =1;

Here, points DESC selects the highest score first.

If two players have the same score, id ASC acts as a tie-breaker.

This makes the result deterministic.

Select the Lowest Value Instead

The same technique can be used to find the minimum value in each group.

With a correlated subquery:

SELECT*FROM athletes a1WHERE points = (SELECT MIN(a2.points)FROM athletes a2WHERE a1.team = a2.team);

Alternatively, with ROW_NUMBER():

SELECT    id,    team,    pointsFROM (SELECT        id,        team,        points,        ROW_NUMBER() OVER (            PARTITION BY teamORDERBY points ASC        ) AS rnFROM athletes) rankedWHERE rn =1;

The only major change is the sort direction.

For maximum:

ORDERBY points DESC

For minimum:

ORDERBY points ASC

Select the Latest Row for Each Group

This pattern is also extremely useful for finding the latest record for each customer, product, account, or other group.

Suppose you have:

customer_idorder_dateorder_amount

With MySQL 8.0+, you can use:

SELECT    customer_id,    order_date,    order_amountFROM (SELECT        customer_id,        order_date,        order_amount,        ROW_NUMBER() OVER (            PARTITION BY customer_idORDERBY order_date DESC        ) AS rnFROM orders) rankedWHERE rn =1;

This returns the latest order for each customer.

The same concept applies to:

• Latest transaction per customer
• Most recent login per user
• Latest price per product
• Most recent status per account
• Highest sale per store
• Latest application record per customer

Important: MAX() Does Not Mean “Latest Row”

A common mistake is to assume that:

MAX(id)

always represents the latest record.

That is only safe when the id column is guaranteed to increase consistently with the event you are measuring.

If you specifically need the most recent date, use the date column:

ORDERBY created_at DESC

or:

MAX(created_at)

depending on the query design.

For example:

SELECT*FROM orders o1WHERE order_date = (SELECT MAX(o2.order_date)FROM orders o2WHERE o1.customer_id = o2.customer_id);

This finds the row or rows with the latest order date for each customer.

Which Method Should You Use?

There are several ways to solve the “maximum value per group” problem.

If you are using MySQL 8.0 or later, window functions are often the cleanest approach for complex top-per-group problems.

For example:

SELECT*FROM (SELECT        a.*,        ROW_NUMBER() OVER (            PARTITION BY teamORDERBY points DESC        ) AS rnFROM athletes a) xWHERE rn =1;

If you need to preserve ties:

SELECT*FROM (SELECT        a.*,        RANK() OVER (            PARTITION BY teamORDERBY points DESC        ) AS rnkFROM athletes a) xWHERE rnk =1;

Frequently Asked Questions

How do I select the row with the maximum value in MySQL?

You can use a correlated subquery:

SELECT*FROM athletes a1WHERE points = (SELECT MAX(a2.points)FROM athletes a2WHERE a1.team = a2.team);

How do I find the maximum value for each group?

Use GROUP BY with MAX():

SELECT    team,    MAX(points) AS max_pointsFROM athletesGROUPBY team;

However, this returns the maximum value rather than the complete corresponding row.

How do I return all columns from the row with the maximum value?

Use a correlated subquery, a join, or a window function such as ROW_NUMBER().

How do I select the highest value for each group in MySQL 8?

A window function is a convenient option:

SELECT*FROM (SELECT        a.*,        ROW_NUMBER() OVER (            PARTITION BY teamORDERBY points DESC        ) AS rnFROM athletes a) rankedWHERE rn =1;

How do I keep ties when selecting the maximum row?

Use RANK():

SELECT*FROM (SELECT        a.*,        RANK() OVER (            PARTITION BY teamORDERBY points DESC        ) AS rnkFROM athletes a) rankedWHERE rnk =1;

Can I find the minimum row instead?

Yes. Replace MAX() with MIN() or change the window-function ordering from DESC to ASC.

Conclusion

Selecting the row containing the maximum value for each group is a common MySQL task in analytics, reporting, and data engineering.

A straightforward solution is the correlated subquery:

SELECT*FROM athletes a1WHERE points = (SELECT MAX(a2.points)FROM athletes a2WHERE a1.team = a2.team);

This compares each row with the maximum points value for its corresponding team and returns the complete row.

For MySQL 8.0 and later, window functions provide additional flexibility. Use ROW_NUMBER() when you need exactly one row per group and RANK() when you need to preserve ties.

The key distinction is simple: MAX() finds the maximum value, while a top-per-group query finds the row associated with that maximum value.