How to Delete duplicate records from a Table in SQL Server

Introduction

In this tip expolore , how to delete duplicate records from a table in sql server.

Sql Script

drop table ##temp
create table ##temp (id char(3) ,marks int )
create table ##temp2 (id char(3) ,marks int )
insert into ##temp(id,marks) ----Here we are inserting duplicate 
select '001',50 ----records for each ID
union all
select '001',60
union all
select '002',66
union all
select '002',88
union all
select '003',92
union all
select '003',64
union all
select '004',44
union all
select '005',67
----Here we are getting the distinct records and insert then into another Tempory table
insert into ##temp2 select distinct id,max(marks) from ##temp where id in(
select a.id from
(select id,count(id) cnt from ##temp group by id having count(id)>1) a)
group by id
---And delete those duplicate records from original Table
delete from ##temp where id in(
select a.id from
(select id,count(id) cnt from ##temp group by id having count(id)>1) a)
---And again inser the inserted reocrds from temporary Table
insert into ##temp select * from ##temp2 
----Drop the Temporary Table.
drop table ##temp2
---Retrieve the Table which will have only distincts Records
select * from ##temp order by id

Thank you