RE: Using SQL to check for duplicate records

The DISTINCT eliminated the duplicates - this is the opposite of what I 
was trying to do.

Rob Berendt
-- 
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)




"Brian J. Eckenrod" <beckenrod@xxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/06/2004 11:20 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Using SQL to check for duplicate records






I typically use Query Manager for that, but it essentially creates a SQL
statement something like:

SELECT DISTINCT
FIELDS
FROM TABLE

HTH,
Brian

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



_______________________________________________
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.