Results 1 to 6 of 6

Thread: Find duplicates - Unique IDs

  1. #1
    Join Date
    Dec 2002
    Posts
    50

    Find duplicates - Unique IDs

    Greetings -

    I have a table as following:

    lastname first name PermNO
    x a 1
    y b 2
    z c 3
    x a 1
    x a 2
    Wach person should have a unique PermNo. However due to bad data entry and not having the field specified as an ID, there are some people with more than one permNo., even though each person should have only one permno. For example, lastname "x", firstname "a", should always have his permno as 1, it should not be 2. The data above shows what I mean.

    What I need is a query to list me all lastname, first name who have two or more PermNOs.

    Thanks,
    Lava

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can do

    select lastname, firstname, count(*)
    from table
    group by lastname, firstname
    having count(*)>1

  3. #3
    Join Date
    Dec 2002
    Posts
    50
    Hello -

    Thank you for the reply, however the query you wrote is not going to help me. What I want is finding all records who have same first name, last name but more than one PCP. Each indivitual should have one PermNO, the list you gave me finds duplicates. In our case, one person might have one or more records, but he/she should always have same PermNO no matter what.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    This should return all the records with different permno for the same person.

    select a.lastname, a.firstname, a.permno, b.permno
    from table as a
    join table as b
    on a.firstname=b.firstname
    and a.lastname=b.lastname
    where a.permno <> b.permno

  5. #5
    Join Date
    Dec 2002
    Posts
    50
    Hello skhanal -

    Thank you for your reply, the query worked great. However I noticed that if someone has more than a record in the database, and have more than one Perm#, then the query lists all the records. My question to you is how do I rewrite the query to only list distinct values instead of listing all the recods? Please see below, it shows one person with all perm#s listed for all the records we have in the database:

    Lname Fname Perm#
    -------------------- ------------ ---------
    SAM JOHN 1016
    SAM JOHN 1492
    SAM JOHN 1492
    SAM JOHN 1492
    SAM JOHN 1492
    SAM JOHN 1492
    SAM JOHN 1016
    SAM JOHN 1492
    SAM JOHN 1492
    SAM JOHN 1492
    SAM JOHN 1016

    Thanks.

    Lava
    Last edited by Lava; 06-27-2012 at 12:04 PM.

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Did you try adding distinct

    select DISTINCT a.lastname, a.firstname, a.permno, b.permno
    from table as a
    join table as b
    on a.firstname=b.firstname
    and a.lastname=b.lastname
    where a.permno <> b.permno

Posting Permissions

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