PDA

View Full Version : Help with SQLPlus query to find dupicates in table



sueberry
06-29-2006, 06:16 PM
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:



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

Roelf
06-29-2006, 08:03 PM
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

sueberry
06-30-2006, 05:31 PM
Thank you Roelf...that worked great!