What is the MySQL query to select the row with the maximum value in a specific 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: