Finding duplicate records 1

[bjrollet]

I have a table that is only supposed to have one record per ID, however, during a data import, some(many) records have been duplicated. Is it possible to write a query that will return the rows that have a duplicate(or more) entry for the ID field?

 

[Rosko]

This statement returns all duplicate rows of a table with only one key field, if you have a composite key you should use AND in your subquery

select * from 'TABLE' as a
where (select count(*) from 'TABLE' as b where a.id = b.id) > 1


Hope this helps
GL

Rosko

 

[tlbroadbent]

To view duplicated IDs:

Select ID
From Table
Group By ID
Having Count(*) > 1

To view rows with duplicate IDs:

Select *
From table t
Join
(Select ID
From Table
Group By ID
Having Count(*) > 1) d
On t.ID=d.ID Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bjrollet]

Right on the nose! Thanks a million.

 

[bperry]

Here is a variation of RosKo's that should run a little more efficiently, and provides a count of the duplicated records (if that is something you would like.)

select id, Count(*) as TheCount
from YourTable
Group by id
having count(*) > 1