Selecting duplicate records..

[9T96T9]

Hi everyone...

i want to select only the account numbers that have duplicate records in a file...

how will i do it in sql? please teach! ^_^

thnx

[gregmonay]

Re: Selecting duplicate records..

this sql code will list all accounts with duplicate record:

select account#, count(account#) from yourfile group by account# having count(account#) > 1

hope this helps.
Greg

[Mercury]

Re: Selecting duplicate records..

i want to select only the account numbers that have duplicate records in a file...

Join the file on itself and check the Relative Record Number (RRN) on each.

select RRN(T01), T01.Account,  RRN(T02), T02.Account 
from MyFile T01 
Join  MyFile T02
On T01.Account = T02.Account
Where RRN(T02) <> RRN(T01)