Sql server:Delete duplicate records from table

Sql server:Delete duplicate records from table

                            Firstly, we will create a table, where we will insert some duplicate rows to
       understand the topic properly. 
     
Create a Temp table called #tbl_MyName by using the following code:::

          Create Table   #tbl_MyName
               (
                      RowID int identity(1,1) not null,
                      MyID int not null,
                      MyName varchar(50) not null
              )

Now insert some data into this table.

Insert into #tbl_MyName(myid,MyName) values(1,'SANDIP')
Insert into #tbl_MyName(myid,MyName) values(2,'GAIKWAD')
Insert into #tbl_MyName(myid,MyName) values(3,'ADITYA')
Insert into #tbl_MyName(myid,MyName) values(4,'PRANAV')
Insert into #tbl_MyName(myid,MyName) values(5,'SA')
Insert into #tbl_MyName(myid,MyName) values(1,'SANDIP')
Insert into #tbl_MyName(myid,MyName) values(2,'GAIKWAD')
Insert into #tbl_MyName(myid,MyName) values(3,'ADITYA')
Insert into #tbl_MyName(myid,MyName) values(4,'PRANAV')
Insert into #tbl_MyName(myid,MyName) values(5,'SA')
Insert into #tbl_MyName(myid,MyName) values(1,'SANDIP')
Insert into #tbl_MyName(myid,MyName) values(2,'GAIKWAD')
Insert into #tbl_MyName(myid,MyName) values(3,'ADITYA')
Insert into #tbl_MyName(myid,MyName) values(4,'PRANAV')
Insert into #tbl_MyName(myid,MyName) values(5,'SA')

SELECT myid,MyName FROM #tbl_MyName

* Now use the following query to resolve the problem of duplicate rows

delete from #tbl_MyName where RowID in( select B.RowIDfrom #tbl_MyName B inner join
(select RowID,MyID,MyName, row_number() (partition by MyName order by MyID)
as duplicate from #tbl_MyName) A ON A.RowID= B.RowID where A.duplicate>1 )
select MyID,MyName from #tbl_MyName order by MyID asc

Now check the data. No duplicate rows exist in the table.