How to Select the Row with the Maximum Value in a MySQL Column

-- create table 
CREATE TABLE athletes (
  id INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL,
  assists INT NOT NULL,
  rebounds INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 4, 3);
INSERT INTO athletes VALUES (0002, 'Kings', 14, 5, 13);
INSERT INTO athletes VALUES (0003, 'Lakers', 37, 6, 10);
INSERT INTO athletes VALUES (0004, 'Nets', 19, 10, 3);
INSERT INTO athletes VALUES (0005, 'Knicks', 26, 12, 8);
INSERT INTO athletes VALUES (0006, 'Celtics', 15, 1, 2);

-- view all rows in table
SELECT * FROM athletes;

The resultant table confirms the data population, showing the diverse performance statistics for each team’s representative athlete, making it easy to visually identify the maximum points value before running the query:

+----+---------+--------+---------+----------+
| 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 |
+----+---------+--------+---------+----------+

4. Executing the MAX Value Query in Practice

Now that the table is established, we can apply the primary subquery method described earlier. Our objective is to identify which athlete achieved the maximum number of points within this dataset. We are interested in the athlete’s ID, team name, and the points themselves, demonstrating how to retrieve specific fields rather than all columns (*), which is a recommended practice for production queries.

The inner query first calculates MAX(points), determining that the highest score in the table is 37. The outer query then filters the records to find any row where points = 37. This process isolates the specific record(s) corresponding to the record holder efficiently.

We execute the following SELECT query in MySQL:

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

The execution yields the following result, confirming that the athlete associated with ID 3 and the Lakers holds the maximum score:

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

This output clearly demonstrates that only the row corresponding to the maximum value in the points column has been retrieved. The power of this method lies in its simplicity and strict adherence to the defined maximum value, avoiding potential data contamination from other records.

5. Handling Scenarios with Ties and Duplicate Maximums

A critical feature of the subquery method utilizing the equality operator (=) is its native ability to handle ties. If multiple rows share the same maximum value in the specified column, this query structure will successfully return all of them. This behavior is often desired when the objective is to find all record holders, ensuring no top-performing entry is overlooked.

For instance, if we modified the data such that both the Lakers and the Knicks scored 37 points, the MAX() function would still return 37, and the outer query’s WHERE clause would select both rows where points = 37. This ensures comprehensive data retrieval regardless of duplication in the maximum value field.

However, if the goal is strictly to retrieve only one record, even in the event of a tie, alternative methods such as using ORDER BY combined with LIMIT 1 (as detailed below) are necessary. It is important to remember this distinction when designing database queries: the subquery method prioritizes completeness of the maximum value retrieval, while the LIMIT approach prioritizes restricting the result set size.

Note: If there are multiple rows tied with the max value in a particular column, then each of those rows will be returned when using the subquery approach.

6. Alternative Method: Using ORDER BY and LIMIT 1

While the subquery approach is highly readable, an alternative and often more performance-optimized method for selecting the row with the maximum value is to use the SELECT statement combined with ORDER BY and LIMIT. This method sorts the entire dataset based on the target column in descending order and then restricts the output to the top one row.

The key advantage of this approach, especially within highly optimized database systems, is that indexing on the sorted column can dramatically improve performance compared to the two-pass scan required by the subquery method. By sorting in DESCENDING order (DESC), the highest value naturally appears at the top of the result set, making the subsequent LIMIT 1 operation very fast.

The syntax is considerably cleaner and more direct than the nested subquery, simplifying the overall structure of the command:

SELECT id, team, points
  FROM athletes
  ORDER BY points DESC
  LIMIT 1;

A significant distinction here is how ties are handled. If there are multiple rows with the maximum value, LIMIT 1 will arbitrarily return only one of those tied rows, based on internal database ordering (which is often unpredictable without a secondary sort key like id). If you must retrieve a specific tied record, adding a secondary ORDER BY criterion (e.g., ORDER BY points DESC, id ASC) is necessary to ensure deterministic results. If the business requirement is to fetch all tied maximums, stick to the subquery method.

7. Performance Considerations for Large Datasets

When dealing with tables containing millions or billions of rows, the choice of query structure can profoundly impact execution time. Both the subquery method and the ORDER BY... LIMIT 1 method have different performance characteristics that must be considered, particularly in high-traffic production environments.

For the subquery method (using MAX() function in the WHERE clause), the database performs two separate operations: a full scan (or index lookup) to calculate the maximum value, and then another scan/lookup to retrieve the corresponding rows. If the column being queried (e.g., points) is properly indexed, the index can be used to quickly find the highest value without scanning the entire table data, significantly optimizing the first step, but the two-pass nature remains a factor.

Conversely, the ORDER BY... LIMIT 1 method relies on sorting the data. While sorting can be computationally intensive, modern SELECT optimizers are highly efficient at utilizing composite indexes to find the top 1 row quickly. If an index exists on the column (points), the database can often perform a “covering index scan” or simply walk the index backwards to find the maximum value and the required row data rapidly, often outperforming the two independent scans of the subquery method, especially when only retrieving the top result.

Best practice dictates that the column being used for finding the maximum value should always be indexed. This dramatically speeds up both the calculation of the maximum value and the sorting operation. Developers should always test both query structures using the EXPLAIN tool in MySQL to determine which provides the superior execution plan for their specific table size and indexing strategy.

8. Summary of Approaches and Further Study

Choosing the best approach depends heavily on the specific requirements of the application—namely, whether handling ties is mandatory, or if maximizing performance for single record retrieval is the primary concern.

Comparison of Max Value Retrieval Methods

• Method 1: Subquery with MAX() and WHERE: This technique is highly recommended when it is necessary to retrieve all rows that share the maximum value (handling ties gracefully). Its logic is clear and aligns well with standard SQL practices.

• Method 2: ORDER BY… LIMIT 1: This is generally the preferred approach for maximizing performance on very large tables, especially when you only require a single, top record, and the ambiguity regarding which tied record is returned is acceptable.

For complex scenarios involving grouping (e.g., finding the maximum score for each team within its conference), more advanced techniques like window functions (e.g., ROW_NUMBER() or RANK()) or correlated subqueries are often required. These advanced methods offer greater flexibility and better logical partitioning capabilities than simple aggregate queries when dealing with partitioned data sets.

Mastering these foundational techniques is essential for any data professional working with database retrieval operations. We encourage exploration of further tutorials explaining how to perform other common tasks in MySQL, such as calculating averages, grouping data using GROUP BY, and optimizing complex joins.