Mahmoud ElMansy: knowledge meant to be free

Delete duplicate rows from table without primary key in Sql Server

This is Friday and im writing this post to complete a previous one titled

SqlServer-Delete Duplicate Records Rows

In this post i will talk about how to delete duplicate rows using one of the built in
sql server functions ROW_NUMBER().
And how to use it to delete Rows in a table without primary key.

First: here is a simple select statement using this function.

WITH tbl  AS(
SELECT id,name,ROW_NUMBER() OVER (PARTITION  BY name ORDER BY name) RowOrder FROM Table_Delete
)
SELECT * FROM tbl

This select statement will select all data in table and add new column to the result named RowOrder
the result is

Second : we will edit the select statement to delete any number of the duplicate rows.

WITH tbl  AS(
SELECT id,name,ROW_NUMBER() OVER (PARTITION  BY name ORDER BY name) RowOrder FROM Table_Delete
)
DELETE FROM tbl WHERE RowOrder>1

this result in deleting all duplicate rows.