Introduction

In this article, you will learn how to delete duplicate rows from a table in SQL Server using the CTE function.

SQL query to delete duplicate rows using CTE

Let’s create a sample table for the demonstration.
There are many duplicate rows for Student marks with the same student name and marks.
In this statement
  1. WITH CTE AS
  2. (
  3. Select ROW_NUMBER() Over(Partition by StudentName, SubMarks Order by StudentId) as 'RowNumber',*
  4. from StudentMark
  5. )
  6. Delete from CTE Where RowNumber > 1
  • First, the CTE uses the ROW_NUMBER() function to find the duplicate rows specified by values in the student name and marks columns.
  • Then, the DELETE statement deletes all the duplicate rows but keeps only one occurrence of each duplicate group.

Summary

In this article, we have learned how to delete duplicate rows from a table in SQL Server.