Revision: 32633
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at September 30, 2010 22:52 by g8rpal
Initial Code
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
Initial URL
http://www.sqlservercentral.com/scripts/duplicate+rows/71078/
Initial Description
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.
Initial Title
Delete duplicate rows from the table using row_number()
Initial Tags
Initial Language
SQL