How to find duplicate rows from query ?

By | May 15, 2017

If you want to find duplicate rows from your table use the following query.

SELECT column1,column2 COUNT(*) users GROUP BY column1, column2 HAVING COUNT(*) > 1

Following are steps :

To test the above query on sample table

Step1 : First of all you need a table (Assuming you have database)

1
2
3
4
5
6
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`age` int(11) NOT NULL
)

Step2 : Insert the dummy values

1
2
3
4
5
6
7
8
9
INSERT INTO `users` (`id`, `name`, `email`, `age`) VALUES
(1, 'shyam', 'shyam@example.com', 31),
(2, 'ram', 'ram@xample.com', 23),
(3, 'shyam', 'shyam@example.com', 31),
(4, 'ram', 'ram@xample.com', 23),
(5, 'shyam1', 'shyam@example.com', 31),
(6, 'ram1', 'ram@xample.com', 23),
(7, 'shyam1', 'shyam1@example.com', 31),
(8, 'ram1', 'ram1@xample.com', 23);

Result would be :

name email COUNT(*)
shyam shyam@example.com 2
ram ram@xample.com 2

 

Leave a Reply

Your email address will not be published. Required fields are marked *