How to delete duplicate records from a table leaving the original record

This tutorial is to delete all the duplicate records from the table leaving the original record.

I have a table "Mytable3", here i am showing this table.

 

 

Now here i am inserting Some duplicate record like this:

insert into mytable3 values(100,'shankar')
insert into mytable3 values(101,'Rani')
insert into mytable3 values(100,'shankar')
insert into mytable3 values(105,'Kamal')

And Now my table will be:

 

 

In this blog our main aim is to remove or delete all those duplicate rows leaving the original rows. So we can implement this using two ways:

• By using temp table
• By using PARTITION  and CTE(common table Expression).

Lets check by using Temp Table

This will take mainly 3-4 steps:

1. Copy all the unique  record to a temp table.
2. Delete all the original table records
3. Copy the temp table record to original table.
4. Drop the Temptable

Lets check by using  PARTITION and CTE(common table Expression)

PARTITION is an operator in sqlserver which divides the result set by checking the  record columns .

This column has to specify by the User.

If this operator same column name it group those record and assign  value to these.



Here i am describing how this  query work.

Inside CTE when the query execute it will give the following result.

Step 1
  

Step 2

Deleting the rows where ticket is greater then 1 in  MyCTE

delete from MyCTE where Ticket>1
It will delete all the rows except 1.

And executing the query once  will give the following result.

 

Thus the query will removed all the duplicate rows  leaving the original records.

Here there is no use of rowno ,you can delete this if you want and make your query simpler as much as possible.