How to find Duplicate Records in MySQL Table - MySQL Developer Tutorial

How to find Duplicate Records in MySQL Table

There are many ways you can find the duplicate records. Let's create sample table customer with some duplicate records and then write queries to find duplicate records in MySQL or MariaDB.

CREATE TABLE `customer` (
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

Insert duplicate records in MySQL Table customer.

insert into customer
(firstname,lastname,age,phonenumber,dob,gender)
values
('Aamir','shahzad',39,'505-424000','1980-01-01','M'),
('Aamir','shahzad',39,'505-424000','1980-01-01','M'),
('Aamir2','',40,'505-4141000','1980-02-01','M'),
('Aamir2','',40,'505-4141000','1980-02-01','M'),
('Aamir3',' ',41,'505-4141000','1980-02-01','M'),
('Aamir5',' ',41,'505-4141000','1980-02-01','M')
;

We are using all the columns to find duplicate records. You can change the query according to columns in which you would like to check the duplicate records.

Use Group by and Having Clause:

We can use Group by Having clause to find the duplicate records. Check the below script

Select firstname,lastname,age,phonenumber,dob,gender,
count(*) as RecordCnt
 from customer 
 group by firstname,lastname,age,phonenumber,dob,gender
 having count(*)>1;

By using Row _Number in MySQL to find Duplicate Record : 

We can use Row_Number to find duplicate records, here first I wrote the query and then used as derived query to filter where Row_Number>1 to get only duplicate records.

select * From (
 Select *,
 ROW_NUMBER() OVER (
 PARTITION BY firstname,lastname,age,phonenumber,dob,gender
 ORDER BY firstname,lastname,age,phonenumber,dob,gender)
 as RowNumber
 from customer) d 
 where RowNumber>1;