Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Finding Duplicates

This is just a short post that uses a contrived example to demonstrate how to find duplicate records in a table. I needed to identify some duplicate records for a supplier recently and I wanted to make some notes on what I did for future reference.


 

First I will create a very simple table for this example


 

CREATE
TABLE [dbo].[Dups]


(

[FirstName] [nvarchar](50)
NULL,

[lastName] [nvarchar](50)
NULL,

[Company] [nvarchar](50)
NULL


)

ON [PRIMARY]


 

I will then add some example data, including some duplicate rows:


 

INSERT
INTO [C_AVG].[dbo].[Dups]


(

[FirstName],

[lastName],

[Company]


)

VALUES (


'Gethyn',


'Ellis',


'GRE'


)


 


 

INSERT
INTO [C_AVG].[dbo].[Dups]


(

[FirstName],

[lastName],

[Company]


)

VALUES (


'Lisa',


'Ellis',


'GRE'


)


 

INSERT
INTO [C_AVG].[dbo].[Dups]


(

[FirstName],

[lastName],

[Company]


)

VALUES (


'Ron',


'Ellis',


'GRE'


)


 

INSERT
INTO [C_AVG].[dbo].[Dups]


(

[FirstName],

[lastName],

[Company]


)

VALUES (


'Lisa',


'Ellis',


'GRE'


)


 

INSERT
INTO [C_AVG].[dbo].[Dups]


(

[FirstName],

[lastName],

[Company]


)

VALUES (


'Lisa',


'Ellis',


'GRE'


)


 


 

When I run a very simple select against this table I get the following output:


 

Gethyn    Ellis    GRE

Lisa        Ellis    GRE

Ron        Ellis    GRE

Lisa        Ellis    GRE


 

as we can see, Lisa is included in this table twice this but if you had a table with a couple million rows in this table and you suspected that it had duplicates spotting the duplicates maybe a little more difficult the following script will identify them for you:


 

This shows that


 

SELECT FirstName, lastName, Company FROM dups

GROUP
BY FirstName, lastName, Company

HAVING (COUNT
(*)
> 1)


 

This returns all the duplicate entries:


 

Lisa    Ellis    GRE


 

This only identifies the rows that exist more than once, cleaning up duplicates through deletion will be covered in another post.


 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.