SQL Script to find duplicate records?

 

Here are some scripts to help to you find Duplicate records or records that occur more than once.

SQL Script to find rows which occur more than once.

This SQL should also work with Visual FoxPro

SELECT colName, COUNT(colName) as NumberOfOccurences FROM TableName GROUP BY colName HAVING (COUNT(colName) > 1 )

 

SQL Script to find duplicate records

SELECT colName FROM TableName GROUP BY colName HAVING (Count(colName) = 2)

 

SQL Script to find records occurring just once

SELECT colName FROM TableName GROUP BY colName HAVING (Count(colName) = 1)

 

SQL Script to find records which occurs more than once based on two columns

SELECT colName1, colName2, COUNT(colName1) as NumOfOccurenceCol1, COUNT(colName2) as NumOfOccurenceCol2 FROM Tablename GROUP BY colName1, colName2 HAVING (COUNT(colName1) > 1) AND (COUNT(colName2) > 1 )

 

SQL Script to find duplicate rows based on multiple columns (more than one column)

SELECT colName1, colName2, COUNT(colName1) as NumOfOccurenceCol1, COUNT(colName2) as NumOfOccurenceCol2 FROM Tablename GROUP BY colName1, colName2 HAVING (COUNT(colName1) = 2) AND (COUNT(colName2) = 2 )

 

SQL Script to Find Rows which occur more than once and display them in descending order of the count of their occurrences

SELECT colName1, COUNT(colName1) as NumberOfOccurences FROM TableName GROUP BY colName1 HAVING (COUNT(colName1) > 1) order by NumberOfOccurences desc