Let's create a table and name it NewTable.

Syntax

  1. Create table NewTable
  3. (
  5. Name varchar(30),
  7. Age int
  9. )

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

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

Now let's see the record count in table.

  1. select count(*) from newTable

Output : 20

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

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

I hope you enjoy this article.

Happy coding J