[thelist] sql de-doupe

[Brian Cummiskey]

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