Re: Using SQL to check for duplicate records

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

where in subselects can be horrible performers...

if you rewrite it as a join...

SELECT a.primarykey as duplicatekey, count(*) as duplicatetimes
 from tablename a  join tablename  b
  on a.primarykey = b.primarykey
  where rrn(a) <> rrn(b)
group by a.primarykey


the count a group is just statistical...


===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"If you pick up a starving dog and make him prosperous,
  he will not bite you. This is the principal difference
  between a dog and a man."

    - Mark Twain

----- Original Message ----- 
From: <rob@xxxxxxxxx>
To: <MIDRANGE-L@xxxxxxxxxxxx>
Sent: Tuesday, January 06, 2004 11:05
Subject: Using SQL to check for duplicate records


> Is there a more efficient way, using SQL, to check for duplicate records
> than the following?
> SELECT A.IPROD, RRN(A) FROM IIM A
> WHERE A.IPROD IN (
>   SELECT B.IPROD FROM IIM B
>   GROUP BY B.IPROD
>   HAVING COUNT(*) > 1)
>
> It works fine on a 3 row dataset, with two of the rows being duplicates,
> or on a 3 row dataset with none of the rows being duplicates.  However on
> a table with 14,036 rows, and no duplicates, it takes forever.
>
>
> Rob Berendt
> -- 
> "All creatures will make merry... under pain of death."
> -Ming the Merciless (Flash Gordon)
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>