Delete Duplicate Record Without Id by using CTE

In some scenario we want to delete duplicate entry when we don't have any id column and one record should be exists in our table. here I am deleting duplicate record by using CTE Function.

 

Step 1: Creating table and inserting duplicate Record.

 

Step 2: Checking Duplicate Record

 

 

 

Step 3: Deleting duplicate Records by using CTE function.

 

 

Above Result is showing only unique record is displaying.

 

I have used Rank function along with partition by then order by.each duplicate  record will generate separate number based on entry like Pen 1,2,3 and book 12,3,4.

 

After generating number i'm deleting record where row number greater then one.