At 15:25 18/08/99 +0200, Richard Ellerbrock wrote:
>Is there a way to display all duplicate records in a table other than to
GROUP BY into a temporary table and to then join back to the original
table? Note that having duplicates in my table is totally legitimate,
except that I want a quick way to find them!
>
>
>--
>Richard Ellerbrock
>richarde@stripped
You don't need to create a temporary table - just use an alias in the SELECT.
Not sure if the syntax is exactly right - still learning MySQL's SQL...
SELECT a.putative_key_1, a.putative_key_2, COUNT(a.putative_key_1)
FROM mytable a, mytable b
WHERE a.putative_key_1=b.putative_key_1
AND a.putative_key_2=b.putative_key_2
GROUP BY a.putative_key_1, a.putative_key_2
HAVING COUNT(a.putative_key_1)>1;
HTH
Colin
