Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Find Duplicates rows

    Please advise on finding duplicates rows based on 3 columns of a table. I am trying to avoid subqueries..

    Example: payment table - policy,amount,coverage are the columns
    duplicate row is when all policy, amount, coverage have same data repeated.

  2. #2
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96

    Re: Find Duplicates rows

    Originally posted by Anchala
    Please advise on finding duplicates rows based on 3 columns of a table. I am trying to avoid subqueries..

    Example: payment table - policy,amount,coverage are the columns
    duplicate row is when all policy, amount, coverage have same data repeated.
    Maybe this query might work (but it depends on what you want to do with the result):

    select policy, amount, coverage, count(*) from payment
    group by policy, amount, coverage
    having count(*) > 1

  3. #3
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Find Duplicates rows

    Another possibility is the ROWNUMBER() OVER (PARTITION BY policy,amount,coverage ORDER BY ... )

    The result from the SELECT is a sequence number column.

    Originally posted by Anchala
    Please advise on finding duplicates rows based on 3 columns of a table. I am trying to avoid subqueries..

    Example: payment table - policy,amount,coverage are the columns
    duplicate row is when all policy, amount, coverage have same data repeated.

  4. #4
    Join Date
    Dec 2003
    Location
    Johannesburg, South Africa
    Posts
    18

    DB2 Information

    HI

    GertK gave you some very useful advise there, thanks Gert.

    For other DB2 DBA;s out there, this query he sent will also assit you in determining within an unique index, what are the duplicates. Just remeber to put the columns of the index in the select and group bu clauses.
    Divvy

Posting Permissions

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