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
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:
How to use Row_Number in SQL Select query:
SELECT ROW_NUMBER() OVER(ORDER BY Age) FROM mytable