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.
CREATE TABLE #temp (RecordID INT, UserID INT, UserName VARCHAR(30), Email VARCHAR(20))
INSERT INTO #temp
SELECT 1, 10, 'Jody Smith', 'email@example.com'
SELECT 2, 15, 'Tom Crane', 'firstname.lastname@example.org'
SELECT 3, 20, 'Jim Jack', 'email@example.com'
SELECT 4, 15, 'Tom Crane', 'firstname.lastname@example.org'
SELECT 5, 25, 'Sara Soll', 'email@example.com'
SELECT 6, 30, 'Ben Trane', 'firstname.lastname@example.org'
SELECT 7, 35, 'Dave Muse', 'email@example.com'
SELECT 8, 10, 'Jody Smith', 'firstname.lastname@example.org'
SELECT 9, 15, 'Tom Crane', 'email@example.com'
SELECT 10, 45, 'Kelly Harp', 'firstname.lastname@example.org'
SELECT * FROM #temp;
SELECT * FROM #temp
WHERE UserID IN
GROUP BY UserID
HAVING COUNT(UserID) > 1
ORDER BY UserID
DROP TABLE #temp;
Running the code above, you will see two datasets:
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.