sql - 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