How to delete duplicate rows from table in sql server?

One of my interviews asked this question. I have two columns Name and Age in my Table(MyTable). From the MyTable I want to delete only duplicate records. So the below Query will help you to delete or remove the duplicate rows from the MyTable.

Below is MyTable output:


SELECT * FROM MyTable

Current output:

Name    Age
vijay    30
antony    40
Aruna    28
antony    40
chander35
mark    42
vijay    30


Remove duplicate rows from the table:
The below query will help you to delete duplicate row from your table. Here we used Row_Number() function and PARTITION BY key words to remove the duplicate rows.

DELETE SUB FROM

(SELECT ROW_NUMBER() OVER (PARTITION BY Name, Age ORDER BY Name) cnt

 FROM MyTable) SUB

WHERE SUB.Cnt > 1

After the query execution the output is:


Name    Age
Aruna    28
antony    40
chander    35
mark    42
vijay    30


How to use Row_Number in SQL Select query:

SELECT ROW_NUMBER() OVER(ORDER BY Age) FROM mytable