find duplicatet values in a table with SQL

gistfile1.txt

# http://www.mximize.com/how-to-find-duplicate-values-in-a-table-
# http://www.petefreitag.com/item/169.cfm

With the SQL statement below you can find duplicate values in any table, just change the tablefield into the column you want to search and change the table into the name of the table you need to search. 

In your recordset you will see the tablefield and how many times it is found as a duplicate. 

SELECT     tablefield, COUNT(tablefield) AS dup_count 
FROM         table
GROUP BY tablefield
HAVING     (COUNT(tablefield) > 1) 

Some further tempering with the statement gets the complete records that are double. (yeah yeah.. no * should be used in the SELECT) It's just for demonstrating folks!! 

SELECT * 
FROM table 
WHERE tablefield IN ( 
 SELECT tablefield
 FROM table 
 GROUP BY tablefield  
 HAVING (COUNT(tablefield ) > 1) 
) 

To go even further in the process and DELETE every double record we could do something like make a temporary table, insert the double records, delete it from the original table and insert the saved single records from the temporary table.