# Database Discussions > Oracle >  Find duplicates - Unique IDs

## Lava

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

----------


## skhanal

You can do

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

----------


## Lava

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.

----------


## skhanal

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

----------


## Lava

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

----------


## skhanal

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

----------

