Hiren Hiren - 5 months ago 16
SQL Question

Show all duplicated rows

Hi 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 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 I can Achieve this.
I have try folllowing query,

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


But this code gives result like

firstname lastname
test test
test2 test2

Ben Ben
Answer

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:

Comments