RE: Using SQL to check for duplicate records

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Single statement and I believe temporary table:
 
WITH T1 AS (SELECT B.IPROD FROM IIM B  
            GROUP BY B.IPROD                         
            HAVING COUNT(*) > 1)                  
SELECT A.IPROD, RRN(A) FROM IIM A, T1 WHERE  A.f1 = T1.IPROD


Elvis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, January 06, 2004 10:05 AM
To: MIDRANGE-L@xxxxxxxxxxxx
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.