In the last issue, we included a tip on deleting duplicate records using SQL. We received a considerable amount of reader feedback on the item. One correspondence from Derek Sutcliffe suggests a variant that he suggests will improve performance, particularly on large files.

Following is Derek's suggested replacement, which lets you view the duplicates to be deleted:

  Select RRN(B) From MyLib/MyFile B, Mylib/Myfile C
    Where B.LastName = C.LastName and RRN(B) > RRN(C)

  Delete From MyLib/MyFile A
    Where RRN(A) in 
     (Select RRN(B) From MyLib/MyFile B, Mylib/Myfile C
        Where B.LastName = C.LastName and RRN(B) > RRN(C))

Thanks for sharing this information with readers, Derek.