List All Rows Containing Duplicates SQL

I want to List All Rows Containing Duplicates by a name in the table for instance, name = TestName or TestName1 or Test name 2.. but also want to know what else is attached to them..

I have done this query.

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

I have found duplicated records but you’re interested in getting all the information attached to them, however this is all on one table..

I have tried to do this..

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

but this is if the data was on 2 separate tables?

Answer

You can use window functions:

select t.*
from (select t.*, count(*) over (partition by firstname, lastname) as cnt
      from table t
     ) t
where cnt > 1

Advertisement

Answer