How to delete duplicate rows in SQL Server

Question

How to delete duplicate rows where no unique row id exists?

The give table is

col1  col2 col3 col4 col5 col6 col7
annie  1    1    1    1    1    1 
annie  1    1    1    1    1    1
shelly 3    3    3    3    3    3
shelly 3    3    3    3    3    3

The final result should look like:

annie  1    1    1    1    1    1
shelly 3    3    3    3    3    3

The queries I tried did not work

Answer 1

CTEs and ROW_NUMBER can be combined together which will allow us to see which rows are deleted (or updated), so you can just change the DELETE FROM CTE... to SELECT * FROM CTE:

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

For example:

COL1    COL2    COL3    COL4    COL5    COL6    COL7
annie    1        1       1       1       1       1
shelly   3        3       3       3       3       3

This example finds the duplicate values by a single column col1 because of the PARTITION BY col1. 

Add PARTITION BY if you want to add multiple columns

ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)