MS SQL Server - find duplicates

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

find duplicates

Johnfli

2006-02-06, 8:23 pm

What is the query needed to find the records that has a duplicate in a
certain field?

Thanks

P. Ward

2006-02-06, 8:23 pm

You need to GROUP BY the column(s) you are searching for duplicates that have
a count > 1 (ie. HAVING COUNT(*) > 1). For example identify any duplicate
lastnames:

CREATE TABLE dbo.dups
(
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

INSERT dbo.dups SELECT 'Peter', 'Ward'
INSERT dbo.dups SELECT 'John', 'Smith'
INSERT dbo.dups SELECT 'Jeff', 'Brown'
INSERT dbo.dups SELECT 'Dave', 'Smith'
INSERT dbo.dups SELECT 'Alex', 'Moon'

SELECT lastname
FROM dbo.dups
GROUP BY lastname
HAVING COUNT(lastname) > 1

HTH

- Peter Ward
WARDY IT Solutions

"Johnfli" wrote:

> What is the query needed to find the records that has a duplicate in a
> certain field?
>
>
> Thanks
>
>
>

Sponsored Links