How Can You Find The Rows With Equal Columns?

If I have a table with important 2 columns, CREATE TABLE foo (id INT, a INT, b INT, KEY a, KEY b); How can I find all the rows that have both a and b being the same in both rows?

Solution 1:

SELECT*FROM foo firstJOIN foo secondON ( first.a = second.a
       AND first.b = second.b ) 
  AND (first.id <> second.id )

Should come up with all the rows where more that one row has the same combination of a and b.

Just hope you have an index on columns a and b.

Solution 2:

select * from foo where a = b

Or am I missing something?

===

Update for clarity:

select * from 
foo as a
inner join foo as b
on a.a = b.a AND b.a = b.b
and a.id != b.id

++++++++++ After 3rd clarity edit:

select f1.id
FROM foo as f1
INNER JOIN foo as f2
ON f1.a = f2.a AND f1.b=f2.b AND f1.id != f2.id

But I'm shot, so check it yourself.

Solution 3:

Try this:

With s as (Select a,b from foo groupby a,b havingCount(1)>1)
Select foo.*from foo,s where foo.a=s.a and foo.b=s.b

This query should show duplicate rows in the table foo.

Solution 4:

Could you please clarify what you need to do ultimately? The best solution may depend on that (e.g., do you simply want to delete all dupliucate-key rows?)

One way is to handle this table (not sure if mySQL supports it, it's from SYBASE) if all you want is unique-keyed rows:

SELECTMIN(id), A, B FROM FOO GROUPBY A, B HAVINGCOUNT(*)>1

Your exact question (although I'm a bit at a loss as to why you'd need all rows except id=2) is:

SELECT F1.*FROM FOO F1 , 
     (SELECT A, B FROM FOO GROUPBY A, B HAVINGCOUNT(*)>1) F2
WHERE F1.A=F2.A and F1.B=F2.B

To delete all the duplicates, you can for example do

DELETE FOO WHERENOTEXISTS
(SELECT1from
    (SELECTMIN(id) 'min_id'FROM FOO GROUPBY A, B HAVINGCOUNT(*)>1) UINIQUE_IDS 
 WHERE id = min_id)

As an alternative, you can do

SELECTMIN(id) 'id', A, B INTO TEMPDB..NEW_TABLE 
  FROM FOO GROUPBY A, B HAVINGCOUNT(*)>1TRUNCATETABLE FOO
  //Drop indices on FOO
  INSERT FOO SELECT*FROM NEW_TABLE
  // Recreate indices on FOO

Solution 5:

shouldn't this work?

SELECT*FROM foo WHERE a = b

=== edit ===

the how about

SELECT a, bFROM foo GROUP BY a, b HAVING COUNT(*) > 1

=== final re-edit before i give up on this question ===

SELECT foo.*FROM foo, (
   SELECT a, b FROM foo GROUPBY a, b HAVINGCOUNT(*) >1
) foo2
WHERE foo.a = foo2.a AND foo.b = foo2.b