How to find and then delete duplicate entries in our SQL Server database.

I've used two different methods, and one seems to work in one scenario,
And the other seems to work in another scenario.

Important

Loss of Data [Duplicate your Table (or) Backup your database« before performing any of these actions]

First, let's find the duplicate entries.
[IDColumn] will be the [Primary Key] column.
[TableName ]will, of course, be the Table Name.

[SQL Server - Find Duplicate Entries]

CFFCS | CarrzSynEdit: | SQL Script


SELECT IDColumn, COUNT(*)
FROM TableName
GROUP BY IDColumn
HAVING COUNT(*) > 1

Here is the first method.
[ColumnName] = The Column you will be deleting the duplicates from.
(As I stated above, this sometimes DOES NOT work correctly, and will delete more than it should in some cases.)

[SQL Server - Delete Duplicate Entries]

CFFCS | CarrzSynEdit: | SQL Script


DELETE FROM TableName
    WHERE IDColumn NOT IN
    (
        SELECT MAX(IDColumn)
        FROM TableName
        GROUP BY ColumnName
    );

The second method worked great for my needs in this latest scenario.
We are using the table twice, which is why we have the [c1] and [c2] names.
Then [<] will delete all duplicates except the LAST record.

[SQL Server - Delete all duplicates except the LAST record]

CFFCS | CarrzSynEdit: | SQL Script


DELETE c1
FROM TableName c1
JOIN TableName c2
ON c1.ColumnName = c2.ColumnName AND c1.IDColumn < c2.IDColumn;

To save the FIRST record and delete all others, change it to. [>]

[SQL Server - Save the FIRST record and delete all others]

CFFCS | CarrzSynEdit: | SQL Script


DELETE c1
FROM TableName c1
JOIN TableName c2
ON c1.ColumnName = c2.ColumnName AND c1.IDColumn > c2.IDColumn;