Need to find duplicates in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to find duplicates in a table

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-11-19 : 10:17:07

Hi,

I have three fields:
id
fName
lName

sometimes there are duplicates, and the ID repeats for each fname,lname combination.

i can't figure out how to find those.

i tried to do a count, but that returns everything (including 1s)

how can i get it to only return repeatings id's per fName,lName combination?

Thank you

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 10:19:27

select t1.fName, t1.lName, t1.ID
from tbl t1 join
(
select fName, lName
from tbl
group by fName, lName
having count(*) > 1
) t2
on t1.fName = t2.fName
and t1.lName = t2.lName

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-19 : 10:30:28

Where are the duplicate values?
In ID column?
In name columns?

Please give an example and tell us the version of SQL Server (2000 or higher?).

No, you're never too old to Yak'n'Roll if you're too young to die.

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-11-19 : 10:41:32

hm, it still gives me everything...

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-11-19 : 10:43:40

i'm using Access SQL, the duplicates are in IDs'

so for example

there could be two John Does, both with IDs = 1
i need to find only those where, all three fields duplicate and see how many times they repeat...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 11:00:19

select id, fName, lName, count(*)
from tbl
group by id, fName, lName
having count(*) > 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources