How to Remove Duplicate Rows in SQL Server?

I first need a table that has duplicate records to remove, so here is a script to create some duplicates in an email address table.

create table email_list
(
name varchar(100)
,email varchar(300)
)
go
insert into email_list values ('Jan','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');

Now, the first method to remove the duplicates is in the following script that I adapted from Alexander’s Post on duplicates.

WITH list_rownumbers AS
(
SELECT name,email,
ROW_NUMBER() OVER (ORDER BY name,email) AS 'RowNumber'
FROM email_list
)
DELETE list_rownumbers WHERE RowNumber not in
(SELECT min(RowNumber) FROM list_rownumbers GROUP BY name,email)

If you just look at the SQL With statement (CTE), you will see that it simply numbers all of the name, email combinations using the Row_Number function. Then it uses the minimum rows number per name, email group as an exclusion to remove the duplicate rows.

The second script is and adaptation of Ritesh’s Post on duplicates.

WITH list_duplicates (name, email, duplicate_count) AS
(
SELECT name,email,
ROW_NUMBER() OVER(PARTITION BY name,email ORDER BY name,email) AS duplicate_count
FROM email_list
)
DELETE
FROM list_duplicates
WHERE duplicate_count > 1

If we focus on the CTE query, we see that is uses the Partition By statement to actually reset the row_number count on each name, email group.

It makes the delete statement a little easier to follow since all we have to do is delete where the row_number (duplicate_count) is larger than 1.

And that’s it.  That is how you Delete Duplicates in SQL.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

SQL Training Online: http://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline