This was a challenge given to me by my colleague few days ago He told me there are some rows in a table which are identical Not a single field change. but I want to delete all the other rows.. and Keep Only one intact This is what i did


declare @var as int
select @var=(cast(count(empid) as int)) from city where city='bengal' and empid=3
delete top(@var-1)city from city where city='bengal' and empid=3

--here city is table name and there are 2 columns empid and city. --In sql server 2005 and above you can do by following way