MySQL - finding duplicate tuples

Question

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.

scott · Accepted Answer · 2009-03-10 03:33:01Z

4

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.

answered Mar 10, 2009 at 3:33

scott

7666 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1