[thelist] sql de-doupe
Brian Cummiskey
brian at hondaswap.com
Sat Feb 2 01:38:14 CST 2008
Hi guys,
I'm working with MS sql 2000 here.
I have a temp table that i need to de-doupe. It has multple 'models'
due to categories
modelA cat1
modelA cat2
modelB cat1
modelB cat2
etc...
Using distinct won't work, so I came up with a de-doupe process. It
works... but it works too good :) I'm losing some valid records,
meaning that it's either not duplicated, or it is duplicated but neither
gets in.
here's my block: anyone see anything wrong?
-- SET UP
declare @modelcount int
declare @temp3 table
( model varchar(100)
)
-- GRAB DISTINCT MODELS FROM WORKING TABLE (@temp2) and place into this
simple @temp3 table
insert into @temp3
select model from @temp2
group by model
-- get count of @temp3
select @modelcount = @@rowcount
-- loop one at a time and pull all data from @temp2 for only 1 instance
of model # into @temp4
-- remove that model from @temp3, then recount @temp3
while @modelcount > 0
begin
insert into @temp4
select top 1 * from @temp2 where model = (select top 1 model from
@temp3 order by model asc)
delete from @temp3 where model = (select top 1 model from @temp3 order
by model asc)
select @modelcount = count(*) from @temp3
end
More information about the thelist
mailing list