MySQL - SELECT rows with MAX(Column value), DISTINCT by another column?

SELECT rows with MAX(Column value), DISTINCT by another column in MySQL?

You have this table class

id	name	subject	score
1	Anna	Math	10
2	Anna	CS	9
5	Jack	Math	9
3	Jill	CS	6
4	Will	Geo	5
6	Will	CS	8
7	Jack	Geo	10
8	Anna	Geo	4

And you want to list the highest score of each student

Run this query:

SELECT
	*
FROM
	class
	INNER JOIN (
		SELECT
			name,
			MAX(score) AS Maxscore
		FROM
			class
		GROUP BY
			name) topscore ON class.name = topscore.name
	AND class.score = topscore.maxscore;

You’ll get this

id	name	subject	score
1	Anna	Math	10
6	Jack	Geo	10
4	Jill	CS	6
5	Will	CS	8

---

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.