[thelist] sql de-doupe

Ken Schaefer Ken at adOpenStatic.com
Sat Feb 2 03:06:04 CST 2008


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 !



More information about the thelist mailing list