Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Dec 2004
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Help with SQLPlus query to find dupicates in table

    Hi, I am trying to design a query in SQLPlus to find duplicate entries in a table and was wondering if someone could help me with the query. Basically I want to find duplicate name entries that have matching SSN. So, I want to run the query on the fields first_name, last_name and SSN and return all the rows in the table that have more than one entry where the first name, last name and SSN are equal. I tried to run the find duplicates query in Access, but it was giving me an error, so I tried running it in SQL and it gave me a different error, here's my query and the error:

    Code:
    SQL> SELECT FIRST_NAME, LAST_NAME, SSN
      2  FROM IDNT
      3  WHERE (((FIRST_NAME) In 
      4  (SELECT [FIRST_NAME] FROM [IDNT] As Tmp 
      5  GROUP BY [FIRST_NAME],[LAST_NAME],[SSN] 
      6  HAVING Count(*)>1  And [LAST_NAME] = [LAST_NAME] 
      7  And [SSN] = [IDNT].[SSN])))
      8  ORDER BY FIRST_NAME, LAST_NAME, SSN;
    WHERE (((FIRST_NAME) In
                         *
    ERROR at line 3:
    ORA-00920: invalid relational operator
    Any help or suggestions on this issue would be greatly appreciated.

    Thanks,
    Shannon

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    SELECT FIRST_NAME, LAST_NAME, SSN, COUNT(SSN) As NUMBER
    FROM IDNT
    GROUP BY FIRST_NAME, LAST_NAME, SSN
    HAVING COUNT(SSN) > 1

    I think this will work
    I am the luckiest man in the world

  • #3
    New Coder
    Join Date
    Dec 2004
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Roelf...that worked great!


  •  

    Posting Permissions

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