CODEWITHSUNDEEP
mysqlduplicates
user3649739
user3649739

Reputation: 1869

Get all the records that are duplicates not just the list of them Mysql

I can do

Select FieldA,FieldB,FieldC,Count(*) from TableA Group By FieldA,FieldB having count(*)>1

Which will give me a list of all the FieldA,FieldB duplicates with a count for each. What I need is all the records in that subset. If a specific FieldA,FieldB combo has a count of 3 I need to see all 3 of those records. I've tried various joins to no avail.

Upvotes: 0

Views: 22

Answers (2)

juergen d
juergen d

Reputation: 204904

select a1.* 
from TableA a1
join
(
   Select FieldA, FieldB
   from TableA 
   Group By FieldA, FieldB 
   having count(*) > 1
) a2 on a1.FieldA = a2.FieldA
    and a1.FieldB = a2.FieldB

Join the same table on the result of the grouped one.

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

Just JOIN your table to a derived table that contains all FieldA,FieldB pairs along with the corresponding count of each pair:

select t1.*, t2.cnt
from TableA t1
join (
  Select FieldA, FieldB, Count(*) as cnt
  from TableA 
  Group By FieldA, FieldB 
  having count(*) > 1
) as t2 on t1.FieldA = t2.FieldA and t1.FieldB = t2.FieldB

Upvotes: 1

Related Questions