/ Published in: SQL
To remove the duplicate rows from table we may have used several ways.
In SQL Server 2005 has the feature to delete the duplicate rows within single query.
In SQL Server 2005 has the feature to delete the duplicate rows within single query.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
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) GO INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(1,'James',25,DEFAULT) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(1,'James',25,DEFAULT) 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(2,'Lisa',24,0) 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(3,'Mirsa',23,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)T WHERE T.RowNumber > 1 SELECT * FROM #temp
URL: http://www.sqlservercentral.com/scripts/duplicate+rows/71078/