Finding Duplicate Records in SQL

Duplicate records can be found in SQL by using the GROUP BY and HAVING clauses along with the COUNT() function. These tools help group similar values and check how many times they appear in a table. They help in:

• Grouping records that have the same values.
• Counting how many times each value appears using COUNT().
• Filtering groups using HAVING COUNT(*) > 1.
• Identifying duplicate rows for data cleaning and analysis.

Syntax:

SELECT column1, column2, ..., COUNT(*)
FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;

• column1, column2: The columns used to check for duplicate values.
• COUNT(*): Counts how many records exist in each group.

Example of Finding Duplicate Records in SQL

Let’s create a table to find duplicate records in a table using SQL. We will use the GROUP BY and HAVING clauses with the COUNT() function to identify duplicates.

1. Identify Duplicate Records

In the above table, we can see there are 2 records with the same geek rank of 5. GeekID 107 and GeekID 108 are having the same rank of 5. Now we need to find this duplication using SQL Query.

Query:

SELECT GeekRank, COUNT(*) AS DuplicateRanks
FROM Geeks
GROUP BY GeekRank
HAVING COUNT(*) > 1;

Output:

• Same GeekRank rows are grouped.
• DuplicateRanks shows the count.
• GeekRank = 5 appears twice (GeekID 107, 108).

2. Deleting Duplicate Records

Once duplicates are identified, you can remove them using the DELETE statement. However, ensure we retain one copy of the duplicate record if needed to preserve important data. This approach helps maintain data integrity while removing redundancy.

Query:

DELETE FROM Geeks
WHERE GeekID NOT IN (
 SELECT * FROM (
 SELECT MIN(GeekID)
 FROM Geeks
 GROUP BY GeekRank
 ) AS temp
);

Output:

• Retains only one record per GeekRank (based on the minimum GeekID) and deletes the rest.
• Use only when GeekRank is treated as a unique identifier for duplicates.