Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Question Unanswered: Finding duplicates in the table

    source table AAA
    --------------------

    pvn_acct_id
    acct_id

    no primary key, there can be duplicates, for ex:

    pvn_acct_id acct_id
    ======= =========
    111 111
    111 111
    111 111

    222 333
    222 334
    222 335

    333 222
    334 222
    335 222

    from first set, using the below query to extract data, it will result in 111,111, for 2nd set, 222, 335, for third set, 335, 222.

    select distinct a.pvn_acct_id, a.acct_id
    from owner.AAA where a.acct_id = (select max(b.acct_id) from tstg.t_acct_num_hist b
    where a.pvn_acct_id = b.pvn_acct_id)
    and a.pvn_acct_id = (select max(c.pvn_acct_id) from tstg.t_acct_num_hist c
    where a.acct_id = c.acct_id)

    Above query is directly populating the target table BBB

    What I want to find out is a sample of data set where i can find different instances of acct_id where pvn_Acct_id is same, and vice versa.
    For ex:

    I want to know where pvn_acct_id is 222, acct_id is 333,334,335, not just acct_id=335.

    pls help.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question

    Did you try this:

    Code:
    
    select distinct a.pvn_acct_id, a.acct_id
    from owner.AAA a
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    Re: Finding duplicates in the table

    select pvn_Acct_id from owner.AAA aaa where acct_id in (select acct_id from owner.BBB bbb
    where aaa.pvn_acct_id != bbb.pvn_acct_id) order by pvn_Acct_id

    select Acct_id from owner.AAA aaa where pvn_acct_id in (select pvn_acct_id from owner.BBB bbb
    where aaa.acct_id != bbb.acct_id) order by Acct_id

    now this query works. got it with trial and error. have fun testing.
    Last edited by cowamoo; 09-23-03 at 19:08.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •