Let's create a table and name it NewTable.
Syntax
- 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 J