--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 dataSELECT 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 tableDELETE TempFROM (SELECT ID, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id ) RN FROM Test) Temp WHERE Temp.RN>1select * from test
Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
Login to post response