Posted By

g8rpal on 09/30/10


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


Delete duplicate rows from the table using row_number()


 / Published in: SQL
 

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

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.

  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

Report this snippet  

You need to login to post a comment.