How to find duplicates in a Teradata table
We can use GROUP BY clause to find how many times the value is duplicated in a table. Group by clause will show just one record for each combination of values. Then we can apply the filter condition using Having and Count(*) > 1 to extract the value that present more than one time in a table.
Sample Table with duplicates
Find duplicates with one field
The First Name and Last Name fields having the duplicate values in the Employee table. Lets find the duplicates for the Last Name alone using the below query.
Query :
|
1 2 3 4 |
SELECT LastName,COUNT(*) FROM Test_DB.employee GROUP BY LastName HAVING COUNT(*) > 1 |
Output:
Find duplicates with more than one fields
Since the FirstName and Last Name having the duplicates, we can use the same Group by clause to find the duplicates by associating the columns.
Query
|
1 2 3 4 |
SELECT FirstName,LastName,COUNT(*) FROM Test_DB.employee GROUP BY FirstName,LastName HAVING COUNT(*) > 1; |
Output
Duplicates for the First Name and Last name in Employee table
