Sometime we require finding duplicate records in our table.  There are several ways to it and I will keep posting separate article for all ways to do the same task. Here is one very short and sweet solution by simple SQL query. Have a look at it.

Well, I am going to create one simple table in AdventureWorks database.

use adventureworks

GO

 

Create Table SelectDuplicate

(

ID int identity(1,1) not null,

Fname varchar(10) not null,

Lname varchar(10) not null,

City varchar(10) not null

)

Now, this is a time to enter some records in just created table above.

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘Ahmedabad’)

INSERT INTO SelectDuplicate VALUES(‘Avi’,‘Sagiv’,‘Edison’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Parsipenny’)

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘WestField’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Ahmedabad’)

So, here is the heart of article below.

SELECT * FROM SelectDuplicate

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

As I mentioned in first paragraph of article as well, there are few different ways to do the same thing which will be explained in coming articles.

Of course, you can delete these duplicate records as well, if you wish. All you need to do is change first “SELECT *” to “DELETE” as below given code.

DELETE FROM SelectDuplicate

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

Reference: Ritesh Shah