I've been watching this thread and wondering what someone would
do with a bunch of duplicate records, so I'll add my 2 cents...
Del dup fro tblName
---- or ----
(using some of that very nice code from Albert earlier)
Create view (col1,Col2,col3) as +
SELECT Col1,Col2,Col3 +
FROM OriginalTable t1, +
(SELECT Col1,Col2,Col3 FROM Original table +
group by Col1,col2,col3 having count(*) > 1) as t2 +
WHERE t2.Col1 = t1.Col1 AND +
t2.Col2 = t1.Col2 AND t2.Col3 = t1.Col3
With a view the dups are always there to check... or if the data is
clean you'll know it because the view won't return anything. Or, just
use the select statement from Albert's code and put it on the
screen w/o creating tables/views.
Of course, it's getting late and I may well be missing something
<g>.
Ben Petersen
>
> >===== 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
>
