In this article we learn that how to delete duplicate row in sql.

Let’s create a table and name it NewTable.

Create table NewTable
(
Name varchar(30),
Age int
)

Now i am going to insert some demo record in my table using while loop.

declare @i int=0
while(@i<5)
begin
insert NewTable values('Record',15)
insert NewTable values('Record1',20)
insert NewTable values('Record2',25)
insert NewTable values('Record3',30)
set @i = @i+1
end

Now let’s see the record count in table.

select count(*) from newTable

Output : 20

Now write a query for common table expression. After that write a query for delete.

with CTE AS(
select row_number() over(partition by name order by name) as RowNo, Name,Age from newTable
)
delete from CTE where RowNo > 1

I hope you enjoy this article.

Happy coding 🙂