MySQL - How to show duplicate records?

Show duplicate records in MySQL

We have table class:

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

We can see there are some records with the same first_name value, and you want to list all rows with the name being duplicated like this:

id	first_name	subject	score
1	Anna	Math	10
2	Anna	CS	9
7	Anna	Geo	4
3	Jack	Math	9
6	Jack	Geo	10

Try this query:

SELECT
	id, class.first_name, subject, score
FROM
	class
	INNER JOIN (
		SELECT
			first_name
		FROM
			class
		GROUP BY
			first_name
		HAVING
			COUNT(first_name) > 1) dup ON class.first_name = dup.first_name;

The result is:

id	first_name	subject	score
1	Anna	Math	10
2	Anna	CS	9
7	Anna	Geo	4
3	Jack	Math	9
6	Jack	Geo	10

If you want to list how many times each record is duplicated, run this:

SELECT
	COUNT(*) c,
	first_name
FROM
	class
GROUP BY
	first_name
HAVING
	c > 1;

The result is:

c	first_name
3	Anna
2	Jack

---

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.