In this article, we learn about find duplicate records from a table in the SQL Server database


SQL Query for finding duplicate records:

Method 1:
SELECT columnName, COUNT(*) AS TotalRecords, Max(ID) AS Duplicates
FROM tableName
GROUP BY columnName
HAVING (COUNT(*) > 1)

Method 2 with CTE:
WITH CTE AS(
   SELECT columnName1,columnName2,
       RowNo = ROW_NUMBER() OVER (PARTITION BY columnName1 ORDER BY columnName1)
   FROM tableName
)
SELECT * FROM CTE WHERE RowNo > 1

Note: PARTITION BY columnName1 represent the partition base column, for example, if we have a customer table and the email column has duplicate email addresses then we make the partition on the base of email column records.

Advertisement

Next
This is the most recent post.
Previous
Older Post

0 comments:

Post a Comment

 
Top