RE: Using SQL to check for duplicate records

And that would be a LOT of duplicates, and different duplicates.  For 
example if A appeared 5,000,000 times as a duplicate, no big deal - it 
would only be in the temporary table once.  However if you have 5,000,000 
different keys, all duplicated, then the table would be bigger.  Still, 
5,000,000 times a 15 byte key is only 75MB.  If that's a real concern on 
your system then you have other issues.

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




qsrvbas@xxxxxxxxxxxx (Tom Liotta) 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/09/2004 05:12 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Fax to

Subject
RE: Using SQL to check for duplicate records






Booth:

That's where count(*) comes into it. Rob's example shows that a second 
SELECT statement must also be executed. In effect, a new temporary table 
is created on the fly by that SELECT. That table contains one row for each 
key that's duplicated. When the primary SELECT executes, each row is 
tested against the temporary table to see if he's dealing with a 
duplicated key or not. If he is, he simply prints the key and the RRN. The 
temporary table fulfills almost the same function as lookahead would -- it 
simply verifies duplication for each row at the time the row is read.

If the temporary table happens to be very large, conceivably you could 
have space problems. Unlikely enough that I wouldn't worry about it unless 
I was dangerously short on space and expected a lot of duplicates.

Tom

midrange-l-request@xxxxxxxxxxxx wrote:

>   9. RE: Using SQL to check for duplicate records (Booth Martin)
>
>One question I have about the SQL solution is about printing the results.
>When/how does one print the RRN of all duplicate records using only SQL?
> 
>-------Original Message-------
> 
>In SQL, just getting count(*) for the groups could take a chunk of
>processing. I'm not sure it could be any more efficient in any other
>language.


-- 
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788 x313
Fax    253-872-7904
http://www.powertech.com


__________________________________________________________________
New! Unlimited Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Act now to get a personalized email address!

Netscape. Just the Net You Need.
_______________________________________________
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.