Re: Finding Duplicate Numbers?

On Tue, 17 Apr 2001 11:30:07 -0700, Colin Putney (cputney@whistler.net) wrote: >On Tuesday, April 17, 2001, at 11:00 AM, Devin Atencio wrote: > >> I have a SQL Table that has over 1,000 records and I was wanting >> to write a script that would find duplicate phone numbers and >> then list the duplicate phone numbers. I have been trying to >> think on how to do this but I can't think of a way. Can someone >> please help me on some code I could write to do this? > >I think you want something like this (MySQL syntax, YMMV): > >SELECT phone_number, count(*) AS instances >FROM my_table >GROUP by phone_number >HAVING instances >= 2 >ORDER BY instances DESC > > >This give you a list of all duplicated phone numbers, the number of >times they appear in the table, and sorts them with the most- >duplicated >numbers first. > >To list them, you just need to loop through the result set echoing >the >phone number. That works for mysql. He probably just wants to kill the duplicates, so there's an easier way: add a unique index to the phone_number field. alter ignore table $table add unique (phone_number); - Mark