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