Quick Look: Deduplicating Data with CTEs and ROW_NUMBER()

I recently ran into a common issue while querying data for a UI component: dealing with potential duplicate records in the database. To solve it cleanly without creating physical temp tables, I used a Common Table Expression (CTE) combined with T-SQL's ROW_NUMBER() window function.

What is a CTE?

A CTE (Common Table Expression) is essentially a temporary, named result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

Think of it as an inline, readable view that exists only for the duration of the query. Unlike #temp tables or @table variables, CTEs require no explicit drop commands or storage overhead in tempdb.

How ROW_NUMBER() and PARTITION BY Help

The ROW_NUMBER() function assigns a sequential integer to each row within your result set. When paired with PARTITION BY, it allows you to reset the row counter for different logical groupings.

For example, instead of numbering all rows continuously from 1 to 10 across your entire dataset, PARTITION BY CategoryId can number rows 1–5 for Category A, and reset back to 1–5 for Category B.

Putting It Together: The Deduplication Pattern

Here is how you can use a CTE and ROW_NUMBER() to identify and eliminate duplicate rows in a single step:

WITH DuplicateCTE AS (
    SELECT 
        CustomerId,
        Email,
        CreatedDate,
        ROW_NUMBER() OVER (
            PARTITION BY Email 
            ORDER BY CreatedDate DESC
        ) AS RowNum
    FROM Customers
)
SELECT 
    CustomerId, 
    Email, 
    CreatedDate
FROM DuplicateCTE
WHERE RowNum = 1;

How This Query Works:

1. PARTITION BY Email: Groups rows sharing the same email address into their own partition.

2. ORDER BY CreatedDate DESC: Ranks the newest record first (RowNum = 1), while older duplicate entries get assigned 2, 3, etc.

3. WHERE RowNum = 1: Filters out all subsequent duplicates, instantly returning only the single most recent record for each email address to your UI!