Trying to find duplicates in table

[roachae]

My SQL isn't the best. Here's my situation:

My table consists of three fields. I'll call them A, B, and C (for simplicity).

For each A, there should be only one B-C combination. No matter how many times A is in the table, it should always have the same B and C. But, there are some duplicates where A will have a different C or even different Bs AND Cs, which I am trying to find.

Can anyone help me with my MySQL query?

[ChrisPadgham]

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a;

This will return the number of combinations of "b" and "c" for each "a"

[smantscheff]

If Mysql isn't the best, as you say, how would you solve this in another DBMS?

[drjohn]

No, he means his knowledge of SQL isn't the best, not the dbms called MySQL isn't the best. As we can tell from the lack of a primary key restriction on column A ;)
There should only be one row for each value of A. Full Stop. You should not allow a second row to be added.

Once you have identified and deleted the offending rows, alter the table to make column A the primary key.

[ChrisPadgham]

Well if you added a unique compound key on A, B, C then it would prevent further rows being added that violates this rule. However that does not address the issue of the existing data that violates this rule.

[roachae]

Thanks, this pretty much did the trick. But now I'm trying to stick in a 'Where CountOfa1 > 1' clause, and I keep getting an error. I'm really only interested in data that's showing up more than once.

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a;

This will return the number of combinations of "b" and "c" for each "a"

[smantscheff]

Try a HAVING instead of a WHERE clause:

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a
HAVING CountOfa1 > 1;

[roachae]

Thanks! That worked great!

Try a HAVING instead of a WHERE clause:

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a
HAVING CountOfa1 > 1;