Select or Delete duplicate records in SQL table

short and sweet way to select or delete duplicate records from SQL-SERVER table.

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

By Ritesh Shah   Popularity  (4136 Views)
Biography - Ritesh Shah
I am Ritesh Shah and currently working as IT Project Leader in one KPO of Environment Laboratory located at NJ, USA area. I have been working with Microsoft technology since last 8+ years and having sound knowledge in SQL-Server, Asp.NET and C#. I have been working as a Project Leader & Pricipal Database Administrator in my current job since 3+ year. You can further read my blogs at SQLHub.Com