How can I select the row with the maximum value by group in MySQL?

---

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

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

This particular example selects the rows with the max value in the points column, grouped by the values in the team column of the table named athletes.

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

Example: Delete Duplicate Rows But Keep Latest 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
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 14);
INSERT INTO athletes VALUES (0003, 'Lakers', 37);
INSERT INTO athletes VALUES (0004, 'Knicks', 19);
INSERT INTO athletes VALUES (0005, 'Knicks', 26);
INSERT INTO athletes VALUES (0006, 'Knicks', 40);
INSERT INTO athletes VALUES (0007, 'Lakers', 21);
INSERT INTO athletes VALUES (0008, 'Celtics', 15);
INSERT INTO athletes VALUES (0009, 'Hawks', 18);
INSERT INTO athletes VALUES (0010, 'Celtics', 23);

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

Output:

+----+---------+--------+
| 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 that we would like to select the rows with the max points value for each team.

We can use the following syntax to do so:

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

Output:

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

Notice that only the rows with the max value in the points column for each team are returned.

For example, there were three players on the Knicks team in the original table that had the following points values: 19, 26 and 40.

Notice that only the row with the Knicks player who scored 40 is returned from this query.

Additional Resources

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