Delete duplicate record in SQL SERVER

Question :- Some time the question arise that how to delete duplicate record from table ?

Answer : firstly we create the table 

CREATE TABLE tblemployee1(
    [empid] int identity(1,1) NOT NULL,
    [empname] [varchar](10) NULL,
    [mgrid] [varchar](10) NULL,
    [test] [varchar](100) NULL)


insert some data on this  like below

insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Cherry',1,'Useful')
insert into tblemployee1 values('Rohit',2,'Useless')
insert into tblemployee1 values('Rohit',3,'Useless')
insert into tblemployee1 values('Usertest',3,'test')
insert into tblemployee1 values('Usertest',3,'test')

Select * from tblemployee1

output is

empid    empname    mgrid         test
1            Cherry          1             Useful
2            Cherry          1             Useful
3            Cherry          1             Useful
4            Cherry          1             Useful
5            Cherry          1             Useful
6            Rohit            2             Useless
7            Rohit            3             Useless
8            Usertest       3              test
9            Usertest       3              test

now we waana to delete the duplicate record which is with name  

the query is  
  
;with cte as(
Select ROw_number() over(partition by empname   order by empname) as col ,* from tblemployee1)
delete from  cte where col>1


then the ouput is

empid    empname    mgrid    test
1              Cherry          1          Useful
6              Rohit            2          Useless
8              Usertest       3          test 

here we use the rownumber for deletion with COMMON TABULAR EXPRESSION.

Hope this will help you 

if any issue then please let me know

Thanks 
ROHIT KUMAR SRIVASTAVA