SQL EXPLORER: Deleting Duplicate Records by using Rank Functions

Reference : http://www.sqlservercentral.com/scripts/duplicate+rows/71078/

IF EXISTS(SELECT * FROM tempdb.Information_Schema.TABLES WHERE Table_Name LIKE '#Temp%')

DROP TABLE #temp

CREATE TABLE #temp ([Id] int, [Name] varchar(50), [Age] int, [Sex] bit default 1)

INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default)

INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0)

INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0)

INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) Values(4,'John',26,default)

INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) Values(5,'Abraham',28,default)

INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) Values(6,'Lincoln',30,default)

DELETE T FROM

(

SELECT Row_Number() Over(Partition BY [ID],[Name],[Age],[Sex]

ORDER BY [ID]) AS RowNumber,* FROM #Temp ) as T

WHERE T.RowNumber > 1

SELECT * FROM #temp

SQL EXPLORER