Posted By

g8rpal on 09/30/10

Tagged

Versions (?)

Last Edited at 09/30/10 10:52pm

Statistics

Viewed 625 times

Favorited by 1 user(s)

Related snippets

Delete duplicate rows from the table using row_number()

/ 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.

Highlighted TextExpand

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/

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.