Member Avatar for RPM1974

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
  • 106 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by CrappyCoder
Member Avatar for CrappyCoder

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?

Member Avatar for RPM1974

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?

Member Avatar for CrappyCoder

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.