X

Get a free quote

Think AI India That Ensures Your IT Runs Seamlessly, Anytime and Every Time

Blog SQL Tip

News & Blog

A Simple Skill That Delivers Big Impact
Deserves Your Attention

Why It Matters At Think AI India Consulting, we consistently see a surprising gap—even among experienced professionals—when it comes to writing SQL queries to detect duplicate records. This is a fundamental but critical skill for ensuring data accuracy, integrity, and quality in modern BI solutions and pipelines.


Whether you’re building a data warehouse, cleansing source files, or preparing reports, the ability to quickly identify duplicates can make or break your data reliability.

Scenario 1: Table With a Composite Primary Key

Goal: Detect duplicate entries in a table where a composite key (e.g., Item_Code + Item_Country) should uniquely identify a row.

Option 1: GROUP BY + HAVING

sql


CopyEdit


SELECT I.Item_Code, I.Item_Country, COUNT(*)


FROM Item I


GROUP BY I.Item_Code, I.Item_Country


HAVING COUNT(*) > 1;


Explanation: This query identifies groups with more than one occurrence of the same key combination—indicating duplicates.


Option 2: CTE + ROW_NUMBER() + COUNT()

sql


CopyEdit


WITH RankedItems AS

SELECT

I.Item_Code,


I.Item_Country,


ROW_NUMBER() OVER (PARTITION BY I.Item_Code, I.Item_Country ORDER BY I.Last_Updated DESC) AS rn,


COUNT(*) OVER (PARTITION BY I.Item_Code, I.Item_Country) AS duplicate_count


FROM Item I

SELECT *

FROM RankedItems


WHERE duplicate_count > 1;


ROW_NUMBER() OVER (PARTITION BY I.Item_Code, I.Item_Country ORDER BY I.Last_Updated DESC) AS rn,


COUNT(*) OVER (PARTITION BY I.Item_Code, I.Item_Country) AS duplicate_count


FROM Item I

Scenario 2: Table Without a Primary Key

In scenarios where no unique identifier exists, business keys (like Email and Phone_number) become your guide.

Option 1: GROUP BY + HAVING

sql


CopyEdit


SELECT e.Email, e.Phone_number


FROM Employees e


GROUP BY e.Email, e.Phone_number


HAVING COUNT(*) > 1;


Use Case: Quickly highlights duplicate employee contact details.


Advanced Option: Using ROW_NUMBER()

sql


CopyEdit


WITH DuplicateEmployees AS

WITH DuplicateEmployees AS ( SELECT Email, Phone_number, ROW_NUMBER() OVER (PARTITION BY Email, Phone_number ORDER BY Email) AS rn FROM Employees )


SELECT Email, Phone_number


FROM DuplicateEmployees


WHERE rn > 1;


  • Easily identify and filter out extra entries.

  • Perfect for deduplication workflows where only the first (or most recent) row is needed.

When Should You Use These Techniques?

1. To prevent duplicate inserts during ETL loads


2. To detect duplicates in incoming files or external feeds


3. To clean up staging or intermediate tables


4. To reconcile mismatches across systems or environments


5. To enhance data accuracy in BI dashboards


6. To maintain ongoing data governance and trust

Final Thoughts: Why This Matters to Your Business

Duplicate records might seem like a small issue—but they can cause major discrepancies in reporting, analytics, and business decisions. With the right SQL skills, you can build cleaner pipelines, create accurate reports, and drive confident decision-making.


At Think AI India Consulting, we help teams build robust, duplicate-free data architectures using best practices across SQL, Microsoft Fabric, and modern data platforms.


We’d love to hear from you! How do you handle duplicate detection in your workflows?

Go Back Top