Count and Delete Duplicate Rows

-- 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
    );