MySQL Select Row with MAX Value in a Column

MySQL Select Row with MAX Value in a Column, you may often need to find the row containing the maximum value in a particular column.

For example, suppose you have a table containing basketball statistics and want to find the athlete with the highest number of points. The MAX() function can identify the highest value, but you may also want to retrieve the other columns associated with that row.

A simple way to accomplish this is to use a subquery:

SELECT id, team, pointsFROM athletesWHERE points = (SELECT MAX(points) FROM athletes);

The inner query finds the maximum value in the points column, while the outer query returns the row or rows containing that value.

This technique is useful in SQL reporting, data analysis, business intelligence, data engineering, and database applications.

Example: Select Row with MAX Value in MySQL

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

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

Next, insert some sample data:

INSERTINTO athletes VALUES(1, 'Mavs', 22, 4, 3),(2, 'Kings', 14, 5, 13),(3, 'Lakers', 37, 6, 10),(4, 'Nets', 19, 10, 3),(5, 'Knicks', 26, 12, 8),(6, 'Celtics', 15, 1, 2);

We can view the complete table using:

SELECT*FROM athletes;

The output is:

+----+---------+--------+---------+----------+| id | team    | points | assists | rebounds |+----+---------+--------+---------+----------+|  1 | Mavs    |     22 |       4 |        3 ||  2 | Kings   |     14 |       5 |       13 ||  3 | Lakers  |     37 |       6 |       10 ||  4 | Nets    |     19 |      10 |        3 ||  5 | Knicks  |     26 |      12 |        8 ||  6 | Celtics |     15 |       1 |        2 |+----+---------+--------+---------+----------+

Suppose we want to select the row containing the highest value in the points column.

We can use:

SELECT id, team, pointsFROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

The output is:

+----+--------+--------+| id | team   | points |+----+--------+--------+|  3 | Lakers |     37 |+----+--------+--------+

The highest value in the points column is 37, so MySQL returns the row belonging to the Lakers.

How the Query Works

The query consists of an outer query and an inner query.

The inner query is:

SELECT MAX(points)FROM athletes;

This returns:

37

The outer query then effectively becomes:

SELECT id, team, pointsFROM athletesWHERE points =37;

Therefore, MySQL returns the row where points equals 37.

This is a useful pattern whenever you need both the maximum value and the other information associated with that value.

Why Not Use MAX() with SELECT *?

You might try:

SELECT MAX(points), *FROM athletes;

This is not the appropriate way to retrieve the complete row associated with the maximum value.

The MAX() function returns the maximum value, but it does not inherently tell MySQL which row’s other columns should be returned.

For example:

SELECT MAX(points)FROM athletes;

returns:

+-------------+| MAX(points) |+-------------+|          37 |+-------------+

But it does not return:

id = 3team = Lakersassists = 6rebounds = 10

The subquery approach solves this by first finding the maximum and then filtering the original table.

Select All Columns from the Row with the Maximum Value

If you want every column from the row with the maximum points, you can use:

SELECT*FROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

The output would be:

+----+--------+--------+---------+----------+| id | team   | points | assists | rebounds |+----+--------+--------+---------+----------+|  3 | Lakers |     37 |       6 |       10 |+----+--------+--------+---------+----------+

This is useful when you need the entire record rather than only a few columns.

What Happens When Multiple Rows Have the Maximum Value?

An important feature of this approach is that all rows tied for the maximum value are returned.

Suppose we add another athlete with 37 points:

INSERTINTO athletes VALUES(7, 'Warriors', 37, 8, 7);

Now two athletes have the maximum score of 37.

Running:

SELECT*FROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

would return both rows:

+----+----------+--------+---------+----------+| id | team     | points | assists | rebounds |+----+----------+--------+---------+----------+|  3 | Lakers   |     37 |       6 |       10 ||  7 | Warriors |     37 |       8 |        7 |+----+----------+--------+---------+----------+

This is important when ties should not be arbitrarily removed.

Select Only the Maximum Value

If you don’t need the corresponding row and only want the maximum value, you can simply use:

SELECT MAX(points) AS max_pointsFROM athletes;

Output:

+------------+| max_points |+------------+|         37 |+------------+

Use this approach when you only need the aggregate result.

Use the subquery approach when you need information from the row containing that value.

Select the Row with the Minimum Value

The same technique works for finding the minimum value.

Simply replace MAX() with MIN():

SELECT id, team, pointsFROM athletesWHERE points = (SELECT MIN(points)FROM athletes);

The minimum points value in the sample data is 14, so the result is:

+----+-------+--------+| id | team  | points |+----+-------+--------+|  2 | Kings |     14 |+----+-------+--------+

If multiple rows contain the minimum value, all tied rows will be returned.

Select the Row with the Maximum Value Using ORDER BY

Another simple approach is to sort the table in descending order and return the first row:

SELECT id, team, pointsFROM athletesORDERBY points DESCLIMIT1;

This returns:

+----+--------+--------+| id | team   | points |+----+--------+--------+|  3 | Lakers |     37 |+----+--------+--------+

However, there is an important difference between this query and the MAX() subquery.

If multiple rows have 37 points, LIMIT 1 returns only one row.

The subquery:

WHERE points = (SELECT MAX(points) FROM athletes)

returns all rows tied for the maximum.

Therefore, choose the approach based on your requirement.

MAX() Subquery vs ORDER BY LIMIT

If ties matter, the MAX() subquery is usually the safer choice.

If you explicitly want one row, ORDER BY ... LIMIT 1 can be simpler.

Adding a Tie-Breaker with ORDER BY

If multiple rows have the same maximum value but you want exactly one row, you can add a secondary sort.

For example:

SELECT id, team, pointsFROM athletesORDERBY points DESC, id ASCLIMIT1;

This first sorts by points from highest to lowest.

If multiple athletes have the same maximum score, the row with the smallest id is selected.

You could instead select the largest id:

SELECT id, team, pointsFROM athletesORDERBY points DESC, id DESCLIMIT1;

This is useful when you need a deterministic single-row result.

Using ROW_NUMBER() in MySQL 8.0+

If you’re using MySQL 8.0 or later, you can also use the ROW_NUMBER() window function.

For example:

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

This assigns rank 1 to the row with the highest points.

Unlike the MAX() subquery, ROW_NUMBER() returns exactly one row even when multiple rows are tied.

You can add a tie-breaker:

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

Selecting the Row with the Highest Value and All Columns

A common requirement is to find the employee, product, customer, transaction, or other record with the highest value while returning all its attributes.

For example, suppose the table contains:

idproductsalescategory

You could use:

SELECT*FROM productsWHERE sales = (SELECT MAX(sales)FROM products);

This returns the complete record or records with the highest sales.

The same pattern can be applied to many business datasets.

Real-World Examples

The maximum-row pattern is useful in many practical situations.

Highest-paid employee

SELECT*FROM employeesWHERE salary = (SELECT MAX(salary)FROM employees);

Most expensive product

SELECT*FROM productsWHERE price = (SELECT MAX(price)FROM products);

Largest transaction

SELECT*FROM transactionsWHERE amount = (SELECT MAX(amount)FROM transactions);

Customer with the highest purchase

SELECT*FROM ordersWHERE order_amount = (SELECT MAX(order_amount)FROM orders);

Stock with the highest price

SELECT*FROM stocksWHERE price = (SELECT MAX(price)FROM stocks);

The underlying concept is the same: find the aggregate maximum first, then return the record matching that value.

Maximum Value Within Each Group

There is an important distinction between finding the maximum value across the entire table and finding the maximum value within each group.

The following query finds the maximum points across the entire table:

SELECT*FROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

If you want the maximum points for each team, you need a different query:

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

The first query finds the overall maximum.

The second query finds the maximum within every team.

This distinction is important when writing analytical SQL queries.

Complete MySQL Example

Here is a complete example you can copy and run:

CREATETABLE athletes (    id INTPRIMARYKEY,    team VARCHAR(50) NOTNULL,    points INTNOTNULL,    assists INTNOTNULL,    rebounds INTNOTNULL);INSERTINTO athletes VALUES(1, 'Mavs', 22, 4, 3),(2, 'Kings', 14, 5, 13),(3, 'Lakers', 37, 6, 10),(4, 'Nets', 19, 10, 3),(5, 'Knicks', 26, 12, 8),(6, 'Celtics', 15, 1, 2);SELECT*FROM athletes;SELECT    id,    team,    pointsFROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

The final query returns:

+----+--------+--------+| id | team   | points |+----+--------+--------+|  3 | Lakers |     37 |+----+--------+--------+

Common Mistakes to Avoid

Using MAX() Without Returning the Corresponding Row

This:

SELECT MAX(points)FROM athletes;

returns the maximum value but not the associated row.

If you need the row, use:

SELECT*FROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

Assuming ORDER BY LIMIT Returns All Ties

This query:

SELECT*FROM athletesORDERBY points DESCLIMIT1;

returns only one row.

If multiple rows have the maximum value and you want all of them, use:

SELECT*FROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

Confusing Overall Maximum with Group Maximum

Overall maximum:

SELECT*FROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

Maximum per team:

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

These queries answer two different questions.

Frequently Asked Questions

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

Use a subquery with MAX():

SELECT*FROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

How do I select all columns from the row with the highest value?

Use:

SELECT*FROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

What happens if two rows have the same maximum value?

Both rows are returned by the MAX() subquery approach.

How do I select only one row with the maximum value?

Use:

SELECT*FROM athletesORDERBY points DESCLIMIT1;

For deterministic results when there is a tie, add another column to the ORDER BY clause.

How do I find the minimum row instead?

Use MIN():

SELECT*FROM athletesWHERE points = (SELECT MIN(points)FROM athletes);

How do I find the maximum value for each group?

Use a correlated subquery, for example:

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

Conclusion

Finding the row with the maximum value in a MySQL table is a common SQL task, especially when you need more than just the maximum number.

The basic pattern is:

SELECT id, team, pointsFROM athletesWHERE points = (SELECT MAX(points)FROM athletes);

The inner query:

SELECT MAX(points)FROM athletes

finds the highest value in the points column. The outer query then returns the row or rows where points matches that maximum.

One major advantage of this approach is that all rows tied for the maximum are returned. If you need exactly one row, ORDER BY ... DESC LIMIT 1 or a MySQL 8.0+ window function such as ROW_NUMBER() may be more appropriate.

Understanding this pattern is valuable because the same technique can be applied to real-world datasets involving maximum salaries, largest transactions, highest sales, most expensive products, top scores, and other business metrics.