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