Listing duplicate records from SQL table

[meldrape]

Hi,

I've been given the "enviable" task of finding duplicates in a database table. The database is SQL7. The table is very old and has over 40,000 records. The table contains client information and the users do a lookup on the telephone number to find the client. The problem is, over the years, users have re-added some of the clients thinking they didn't exist without doing a lookup and the client telephone number was never used as a unique ID. I have found up to three versions of these clients with the same phone number but their names are spelled differently. My questions is, how would I extract a list of duplicate clients? In other words, I'd like to be able to list the clients that have been entered more than once, the common thread being their telephone number. I can do the ASP part, it's the SQL statement I'm having trouble with. Any thoughts would be greatly appreciated.
<select * from clients where phone????>
Thanks.

 

[JimEkleberry]

You're pretty close. The following should work:

select * from clients group by phone

 

[JimEkleberry]

I was thinking about this more and the statement that I provided will not give you what you want.

This should work better:

select * from clients order by phone

Sorry about that.

 

[meldrape]

Thanks, Jim but that will give me everything. I only want the records that are duplicated by the telephone number...

If I have the telephone number 210-555-1212 for MedClinic 1, but it's also listed for MedClinic One and MecClinic #1, then those are the records I need.

If I have the telephone number 210-345-2345 for Dr. Davis and that's it, then I don't want to see Dr. Davis.

Am I making any sense?

Thanks again, Jim!

 

[link9]

select count(*), col1 from someTable
group by col1
having count(*) > 1

is your template for pulling duplicate entries out of a database table. Modify as needed.


paul

 

[meldrape]

Thanks very much to both of you!