Duplicate Records - Obtaining The Unique ID's

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

SQL Server 2000 Forums

SQL Server Development (2000)

Duplicate Records - Obtaining The Unique ID's

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2003-05-19 : 11:37:40

Hello-

I'm trying to write a sproc that will give me the identity field of a row where two of the fields are identical (name in this case).

I've seen the article about selecting duplicate records, so that isn't a problem.

 
select contactFirstName, contactLastName, count(*)
from tblContacts group by contactFirstName, contactLastName
having count(*) > 1
order by count(*) desc, contactFirstName, contactLastName

It would be nice if there was a way for me to also get the identity field for any of these duplicates. I'm thinking that I can't do it within this statement because grouping by an identity field will automatically mean that I get nothing with a count(*) greater than 1.

Is there a way to do this by using this SQL statement as a sub-select or perhaps joining it so that I can get all of the contactID's? Thanks in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-19 : 12:14:50

select t1.id
from tblContacts t1,
(select contactFirstName, contactLastName
from tblContacts group by contactFirstName, contactLastName
having count(*) > 1
) t2
where t1.contactFirstName = t2.contactFirstName
and t1.contactLastName = t2.contactLastName

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

Nick
Posting Yak Master

155 Posts

Posted - 2003-05-19 : 12:25:39

Of course... Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources