Count and delete duplicate rows in table.
-- Finding Duplicates Using `GROUP BY` -- http://www.tech-recipes.com/rx/49959/finding-duplicate-records-using-group-by-in-sql-server/ -- Adding grouping and a counting field to our display of FirstName, LastName -- and MobileNo combination shows how many times each customer’s name appears. -- select FirstName, LastName, MobileNo, COUNT(1) as CNT from CUSTOMER group by FirstName, LastName, MobileNo; -- -- The count CNT shows how many times the row has been duplicated. -- CNT = 1 indicates that row appears only once. -- -- To filter out using the `having` clause excludes rows that appear only -- once. select FirstName, LastName, MobileNo, COUNT(1) as CNT from CUSTOMER group by FirstName, LastName, MobileNo having COUNT(1) > 1; -- -- Count Duplicate Records -- -- The following query demonstrates the usages of `GROUP BY`, `HAVING`, `ORDER -- BY` in one query and returns the results with duplicate column and its count -- in descending order. -- -- http://blog.sqlauthority.com/2007/07/11/sql-server-count-duplicate-records-rows/ -- select table_column, count(*) as total from table_name group by table_column having count(*) > 1 order by count(*) desc; -- -- Delete Duplicate Records -- -- The table must have identity column, which will be used to identify the -- duplicate records. The table has `table_id` as the Identity Column, and -- columns that have duplicate data are `dupe_column1`, `dupe_column2` and -- `dupe_column3`. -- -- http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/ -- delete from table_name where table_id not in ( select max(table_id) from table_name group by dupe_column1, dupe_column2, dupe_column3 );