Thanks, Troy for suggesting what appears to be a workable solution. I'll
try it out as soon as I ahve time.
Stan Loo
----- Original Message -----
From: "Troy Sosamon" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 09, 2001 12:38 AM
Subject: RE: Capturing Duplicates
> Stan,
>
> Do something like this.
>
> Your original table is table1 with id, dup1, dup2, dup3
>
> -- make an empty table just like table1
> project temp table ttt from table1 where limit = 0
>
> -- get your dups -- this returns 1 row for each duplicate
> insert into ttt (dup1, dup2, dup3) select dup1, dup2, dup3 +
> from table1 group by dup1, dup2, dup3 having count * > 1
>
> -- get the records that have the dups
> project temp table ttt2 from table1 where limit = 0
> insert into ttt2 (id, dup1, dup2, dup3) select +
> t1.id, t1.dup1, t1.dup2, t1.dup3 from table1 t1, ttt t2
> where t1.dup1 = t2.dup1 and t1.dup2 = t2.dup2 and t1.dup3 = t2.dup3
>
>
> Troy
>
> >===== Original Message From [EMAIL PROTECTED] =====
> >Now that you have the #'s of each duplicates, they can be expanded to
another
> >table to exactly represent the original duplicates using declare cursor
and
> an
> >IF...ENDIF loop.
> >RRR
> >
> >> suredata wrote:
> >>
> >> Thank you Bill, Ron, Dennis, Phil, and Albert for responding.
Apparently I
> >> did not make myself clear enough as to what I want.
> >>
> >> I need to save all duplicate rows to a new table or file, not just
knowing
> >> which rows in the original table have one or more duplicates. In other
> >> words, if a row in the original table has 5 duplicates (or 6 identical
rows
> >> in the table), I want to save these five duplicates as separate rows in
a
> new
> >> table. This new table would contain nothing but all duplicates in the
> >> original table. Is there an easy way to achieve this?
> >>
> >> I hope this is clearer. Thanks again
> >>
> >> Stan Loo
>
> Troy Sosamon
> Denver Co
> [EMAIL PROTECTED]
>
>
