How do I find all the duplicate records in a table?

Question

0.00/5 (No votes)

See more:

I want to find the duplicate values in my table on multiple columns.

But the output looks like

Name city Name city
chandu america chandu america
chandu america chandu america
tom UK tom UK
tom UK tom UK

How can i show only two columns? Is there any easy way of finding all duplicate records?

What I have tried:

select * from EMP a join(select Name,city from EMP group by Name,city having count (*)>1)b
on a.Name=b.Name
and a.city=b.city

Posted 19-Jun-18 19:45pm

chandra sekhar

Updated 26-Jun-18 19:41pm

Add a Solution

3 solutions

Solution 1

To show only two colums, reduce the columns you select:

SQL

select a.Name, a.city from EMP a join(select Name,city from EMP group by Name,city having count (*)>1)b
on a.Name=b.Name
and a.city=b.city

To show distinct results only:

SQL

select distinct a.Name, a.city from EMP a join(select Name,city from EMP group by Name,city having count (*)>1)b
on a.Name=b.Name
and a.city=b.city

Posted 19-Jun-18 19:55pm

User 7429338

Solution 3

To find duplicate records

select Name,city,count(*) Cnt from TableName group by Name,city having count(*)>1

To delete duplicate records from table

;with del as
(select row_number() over(partition by Name,city order by Name,city) as rownumber,* from TableName)
delete from del where rownumber >1

Posted 19-Jun-18 20:32pm

kirthiga S

Updated 19-Jun-18 20:33pm

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-06-19T20:08:00

Solution 2

You need only this:

SQL

SELECT Name, city
FROM EMP
GROUP BY Name, city
HAVING COUNT(*)>1

Another SELECT statement is redundant!

Posted 19-Jun-18 20:08pm

Maciej Los