CODEWITHSUNDEEP
mysql
Leonard
Leonard

Reputation: 13767

MySQL - finding duplicate tuples

I have a MySQL table with multiple fields, and I'd like to find any entries that duplicate a tuple.

I was initially thinking I could do this

select f1, f2, f3 from mytable t1 left join mytable t2 
where t1.f1 = t2.f1 and t1.f2 = t2.f2 and t1.f3 = t2.f3;

But then I realized that will just return all the rows in the table (I think).

I built an index on the tuple. My table has 1.2 million rows in it, if that makes a difference.

My SQL is not very strong. Help appreciated.

Upvotes: 2

Views: 2616

Answers (1)

scott
scott

Reputation: 746

Perhaps something like this:

select f1, f2, f3, count(*)
from mytable
group by f1, f2, f3
having count(*) > 1

I use MS SQL so the syntax might be off.

Upvotes: 4

Related Questions