Advanced Search

Delete duplicate rows from the table using row_number()

/ Published in: SQL
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. IF EXISTS(SELECT * FROM tempdb.Information_Schema.Tables WHERE TABLE_NAME LIKE '#Temp%')
  2.     DROP TABLE #temp
  3.  
  4. CREATE TABLE #temp ([Id] INT, [Name] VARCHAR(50), [Age] INT, [Sex] bit DEFAULT 1)
  5. GO
  6.  
  7. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(1,'James',25,DEFAULT)
  8. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(1,'James',25,DEFAULT)
  9. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(1,'James',25,DEFAULT)
  10.  
  11. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(2,'Lisa',24,0)
  12. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(2,'Lisa',24,0)
  13. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(2,'Lisa',24,0)
  14.  
  15. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(3,'Mirsa',23,0)
  16. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(3,'Mirsa',23,0)
  17. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(3,'Mirsa',23,0)
  18.  
  19. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(4,'John',26,DEFAULT)
  20. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(5,'Abraham',28,DEFAULT)
  21. INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(6,'Lincoln',30,DEFAULT)
  22.  
  23.  
  24. DELETE T FROM
  25. (SELECT ROW_NUMBER() OVER(Partition BY [ID],[Name],[Age],[Sex] ORDER BY [ID]) AS RowNumber,* FROM #Temp)T
  26. WHERE T.RowNumber > 1
  27.  
  28.  
  29. SELECT * FROM #temp

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.