How to find duplicates rows in SQL

Suppose you have a table that contains duplicate data and you want to find duplicate data. To find duplicate data use the below code

SELECT *
FROM TableName
WHERE ID NOT IN
(
     SELECT MAX(ID) FROM TableName
     GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3

)

It finds the duplicates rows, the table must have an identity column, Here ID is the identity column which is used to find the duplicates rows. These are the duplicates columns DuplicateColumn1, DuplicateColumn2, DuplicateColumn3

To delete duplicate rows simply change the SELECT to Delete the code look like this. Run the below query to delete duplicates rows

DELETE
FROM TableName
WHERE ID NOT IN
(
     SELECT MAX(ID) FROM TableName
     GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3

)

If you don't have Identity column then use the below code

SELECT Column_Name1,Column_Name2, COUNT(*)

FROM TableName

GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3

HAVING COUNT(*) > 1