Sean Lange (10/10/2012)
Here is an example of one way of deleting duplicates. There are plenty of other ways to do this.
create table #Dupes
(
IdentityValue int identity not null,
UserValue varchar(20),
AnotherValue varchar(10)
)
insert #Dupes
select 'asdf', 'Another' union all
select 'asdf', 'Another' union all
select 'asdf', 'Another' union all
select 'qwer', 'Another' union all
select 'qwer', 'Another' union all
select 'unique', 'Another' union all
select 'another', 'Another' union all
select 'another', 'Another' union all
select 'asdf', 'Another' union all
select 'qwer', 'Another'
select * from #Dupes
;with cte as
(
select IdentityValue, UserValue, AnotherValue, ROW_NUMBER() over (partition by UserValue, AnotherValue order by IdentityValue) as RowNum
from #Dupes
)
delete cte where RowNum > 1
select * from #Dupes
drop table #Dupes
GOT IT! Thanks!
Here is what I ended up using:
TABLE:
[dbo].[ActivityGroup]
(
[ACTIVITYGROUP_DESC] [nvarchar](255) NULL,
[REQUEST_Id] [int] NULL,
[PullDate] [date] NULL
) ON [PRIMARY]
WITH DUPLICATES AS
(SELECT ROW_RUMBER() OVER (PARTITION BY ACTIVITYGROUP_DESC, REQUEST_Id, PullDate
ORDER BY ACTIVITYGROUP_DESC, REQUEST_Id, PullDate) AS Dups
FROM ActivityGroup
Delete DUPLICATES where Dups > 1
This is a great way to get rid of exact duplicates! Im sure its less over head than my previous approach 🙂 Many Thanks!