Show all duplicated rows

Question

suppose I have following sql table

    objid  firstname lastname active      1       test      test     0      2       test      test     1      3       test1     test1    1      4       test2     test2    0      5       test2     test2    0      6       test3     test3    1 

Now, the result I am interested in is as follows:

     objid  firstname lastname active      1       test      test     0      2       test      test     1      4       test2     test2    0      5       test2     test2    0 

How can I achieve this? I have tried the following query,

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

But this query gives results like

    firstname  lastname      test        test      test2       test2 

Answer 1

You've found your duplicated records but you're interested in getting all the information attached to them. You need to join your duplicates to your main table to get that information.

select *   from my_table a   join ( select firstname, lastname             from my_table            group by firstname, lastname           having count(*) > 1 ) b     on a.firstname = b.firstname    and a.lastname = b.lastname 

This is the same as an inner join and means that for every record in your sub-query, that found the duplicate records you find everything from your main table that has the same firstseen and lastseen combination.

You can also do this with in, though you should test the difference:

select *   from my_table a  where ( firstname, lastname ) in           ( select firstname, lastname             from my_table            group by firstname, lastname           having count(*) > 1 ) 

Further Reading:

• A visual representation of joins from Coding Horror
• Join explanation from Wikipedia

Answer 2

SELECT DISTINCT t1.* FROM myTable AS t1 INNER JOIN myTable AS t2   ON t1.firstname = t2.firstname   AND t1.lastname = t2.lastname   AND t1.objid <> t2.objid 

This will output every row which has a duplicate, basing on firstname and lastname.