SQL duplicates with different primary keys

Question

I have a table in this form:

id | firstname | lastname
---+-----------+----------
1  | alex      | marti
2  | mark      | finger
3  | alex      | marti
4  | ted       | port

Need to return the firstname, lastname duplicates in this form:

1  | alex      | marti
3  | alex      | marti

I tried doing select firstname, lastname from t group by firstname, lastname having count(*) > 1 but that will return something like

firstname | lastname
----------+----------
mark      | finger
alex      | marti
ted       | port

And I need the id of the duplicates but of course select id, firstname, lastname from t group by id, firstname, lastname won't work.

Any ideas? Thanks.

Answer 1

Select Id, First_Name, Last_Name FROM ( Select Id, First_Name, Last_Name, Count() Over (Partition By First_Name,Last_Name) Count FRom Emp ) AS T Where T.Count > 1

Answer 2

select a.* from t a,
(select first, last from t group by first, last having count(*) > 1) b
where a.first = b.first and a.last = b.last

Answer 3

You need to aggregate the id. If you need only the ID of one of them, for, say, deletion, you could do:

select max(id) id, firstname, lastname from t group by firstname, lastname having count(*) > 1

If you want both id's and know there will never be more than 2, you could do the following:

select min(id) minid, max(id) maxid, firstname, lastname from t group by firstname, lastname having count(*) > 1

If you want all duplicates, along with their id's, you'll have to use a derived table, as in Nitin Midha's answer.

Answer 4

select id, firstname, lastname
from table t
where exists (select 1
from table t2
where t2.firstname = t.firstname
and t2.lastname = t.lastname
and t2.id <> t.id)