0

I have a table with roughly 50,000 records. Each record has 25 or so columns. One of which is a phone number column. Some of these records, while most not not the same in any other way (a few are identical), have the same phone number. Id like to delte all but one record from the table so I only have one record per phone number. Thoughts?

2
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by CrappyCoder
0

How would you know which record to keep if the phone numbers are the same but the record information is different?

Do you have a timestamp on each record so you know whioh is the latest record?

0

For our purposes here which record is kept really doesnt matter. We woud want the following:

"IMPORT_DT" = Todays date

and you could take the field "ACCT_BAL" and simply take the largest value of the records with the same Phone Number values.

Does that make sense?

0

The following code...

SELECT	MAX(ACCT_BAL),
         [TelephoneNo]
FROM     [TableName]
GROUP BY [TelephoneNo]
HAVING COUNT(TelephoneNo)>1

Will return all the duplicate telephone numbers and the Largest account balance - you can then put the results of this in a temporary hash table and join onto your original table. Record with the same telephone number but lesser ACCT_BALs can then be deleted from your original table.

Other ways of doing this but really depends on yer SQL knowledge

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.