Find duplicates - Unique IDs

[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