How to delete duplicate data?` [Resolved]

--Sample table

create table test (Id int, FName varchar(5), LName varchar(5))

insert into test values (1,'aaa','aaa'),(1,'ttt','ttt'),(1,'fff','fff')



select * from test



--Delete Query

;with del as(

select Id,ROW_NUMBER()over(order by Id)rn from test

)delete from del where rn>1



select * from test

If this post helps you mark it as answer
Thanks

create table test (Id int, FName varchar(5), LName varchar(5))

insert into test values (1,'aaa','aaa'),(1,'ttt','ttt'),(1,'fff','fff')

--To display only one record from duplicate data
SELECT ID, FName, LName FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id ) RN  FROM Test) Temp 
WHERE Temp.RN=1

-- To Delete duplicate data from original table
DELETE Temp
FROM (SELECT ID, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id ) RN  FROM Test) Temp 
WHERE Temp.RN>1

select * from test

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif