MySQL: How to Select Row with Max Value in Column


You can use the following syntax in MySQL to select the row with the max value in a particular column:

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

This particular example selects the row with the max value in the points column of the table named athletes.

The following example shows how to use this syntax in practice.

Example: Select Row with Max Value in Column in MySQL

Suppose we have the following table named athletes that contains information about various basketball players:

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

Output:

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

We can use the following syntax to select the row with the max value in the points column of the table:

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

Output:

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

Notice that only the row with the max value in the points column is returned.

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

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Use DELETE with INNER JOIN
MySQL: How to Delete Rows from Table Based on id
MySQL: How to Delete Duplicate Rows But Keep Latest

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *