Select Duplicate Records in MS SQL-Server

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



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



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



SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname


Reference: Ritesh Shah

3 Responses to “Select Duplicate Records in MS SQL-Server”

  1. tejasnshah Says:

    Hi Ritesh,

    Its better to use ROW_NUMBER feature in SQL SERVER 2005.

    You can find more detail at:



    • riteshshah Says:

      Hi Tejas,

      Thank you very much for you kind update. The way I described in above article was for general T-SQL and could be run in any version. However, I have written few article for use of RANK() and ROW_NUMBER.

      Thanks you very much once again for your interest in my blog. Do update me whenever you feel so. Constructive criticism is a way towards success.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: