Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: displaying non-duplicate keys of all duplicate entries

    Ok, so I'm checking for duplicate data in a table. Let's say it has 3 data fields and a key field (i.e. "ID", "FIRST", "MIDDLE", "LAST"). No keys are duplicated. If I find entries that have the same data in each of the non-key fields, I want to know the keys for all those entries. I have been able to find duplicate rows using this...

    Code:
    SELECT
        TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
    FROM
        TABLE
    GROUP BY
        TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
    HAVING
        COUNT(*) > 1
    Unfortunately I've found no way to incorporate the return of the TABLE."ID" for every duplicated entry. Is there some way I can join the result with the db.table to find this, or some other way to make this happen?

    Thanks,
    Dean

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sure, use:
    PHP Code:
    SELECT
        A
    ."ID"A."FIRST"A."MIDDLE"A."LAST"
    FROM TABLE AS A
    WHERE 1 
    < (SELECT Count(*)
       
    FROM TABLE AS B
       WHERE  B
    ."FIRST" A."FIRST"
          
    AND   B."LAST" A."LAST"
          
    AND   B."MIDDLE" A."MIDDLE"
    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    Thanks, that worked well, although it takes a good while for the server to process the query.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Indicies would help this query a lot, particularly an index on last, first, middle.

    -PatP

Posting Permissions

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