The basic problem is that in your set of sample records, you have no duplicates. I assume that you mean that the "model" values are duplicated? For each duplicate model record, which category record do you wish to keep? If you can just list (a) your starting record set and (b) your final record set, it usually becomes trivial to write some SQL to do what you want. Cheers Ken -----Original Message----- From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Brian Cummiskey Sent: Saturday, 2 February 2008 6:38 PM To: thelist at lists.evolt.org Subject: [thelist] sql de-doupe 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 -- * * Please support the community that supports you. * * http://evolt.org/help_support_evolt/ For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt !