This problem can be solved by many ways. But I have two simple method :-
1) using simple sub query Method
2) using Rowcount
1) delete
top (select count(*)-1 from TableName where Condition )
from
TableName where Condition .
Note :- Both condition should be same for apropriate result.
2) RowCount is very usefull keyword in sqlserver. By this way we can fix , how many row will be affected .
suppose in a table we have n number of rows , we want to display only 1 row , just write down
set rowcount 1
after that any DML or DDL command effects only one row of table.
and ,
set rowcount 0 , for all rows from table
For the above problem , at first get number of similar rows from table using " select count(*) from TABLENAME where Condtion " (suppose get n rows , where n is integer)
Then
set rowcount n-1
delete from TABLENAME where Condition
set rowcount 0
All duplicate row can be deleted with a single query as
ReplyDeletedelete T from
(
select row_number()over(partition by column1 order by column2)a,* from table)T
where a>1