find duplicates

What is the query needed to find the records that has a duplicate in a
certain field?
Thanks
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
>
>

find duplicates

What is the query needed to find the records that has a duplicate in a
certain field?
ThanksYou need to GROUP BY the column(s) you are searching for duplicates that hav
e
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
>
>