SQL Display Duplicate Rows

Transact-SQL (TSQL) Quick Tips – SQL Display Duplicate Rows

I was recently compiling a distribution list (email) into a flat table.  The structure was simple with name and email address.  In some cases, a user was included on multiple email distribution lists, so they would show up multiple times in the table.  In this case, I wanted to review these duplicate records before making a decision on how to handle them.

The following SQL Statements create a temp table containing duplicate rows, then the SQL statement used to display the duplicate rows for review.


Running the code above, you will see two datasets:


SQL Display Duplicate Rows


As you can see, the main dataset contains duplicate rows for “Jody Smith” (rows 1 and 8), and for “Tom Crane” (rows 2, 4 and 9).  The second dataset simply isolates the duplicate rows for review.  This is handy when you have larger tables, and can be used to test or spot-check any table if you want to ensure duplicate rows are accounted for.

There may be more sophisticated ways to prevent this situation from even happening, but sometimes you either inherit a table with duplicate rows and you need a quick and easy way to display them.


Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA



Leave a Comment