Advanced Search

Delete duplicate rows from the table using row_number() - SQLServerCentral

/ Published in: SQL
Save to your folder(s)

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.