mysql select ONLY duplicate records from database

Question

I'm trying to do something with the duplicate records in a mysql database. however, I don't want to delete the records, and only two columns are duplicate. How can I find just these records?

http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql — gen_Eric, Mar 02 '12 at 22:06

score 10 · Accepted Answer · answered Mar 02 '12 at 22:07

can you post more information about the table structure and what do you mean that some are duplicate but only by two columns ?

Anyway, you can look into the GROUP BY, COUNT, and HAVING

SELECT `duped_field1`, `duped_field2`, COUNT(*) `tot`
FROM `table`
GROUP BY `duped_field1`, `duped_field2`
HAVING `tot` > 1

score 3 · Answer 2 · answered Mar 02 '12 at 22:12

The general principle for finding duplicates is to just use group by and having count(*) > 1

If you just want to know the duplicate column values:

select col1, col2
from table
group by col1, col2
having count(*) > 1

If however you want to see all fields where the two columns are duplicated:

select t.*
from @tbl t
where exists (select * 
              from @tbl d 
              where d.col1 = t.col1 and d.col2 = t.col2
              group by d.col1 
              having COUNT(*) > 1)

or simply add a `*` .. `SELECT *, COUNT(*) tot` – Fabrizio Sep 07 '14 at 18:15 — Fabrizio, Sep 07 '14 at 18:15

mysql select ONLY duplicate records from database

2 Answers2

Linked