List:General Discussion« Previous MessageNext Message »
From:Colin McKinnon Date:August 18 1999 3:58pm
Subject:Re: Finding duplicate records
View as plain text  
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

Thread
Finding duplicate recordsRichard Ellerbrock18 Aug
  • Re: Finding duplicate recordsMartin Ramsch18 Aug
  • Re: Finding duplicate recordssasha18 Aug
  • Re: Finding duplicate recordsColin McKinnon18 Aug
Re: Finding duplicate recordsRichard Ellerbrock19 Aug
  • Re: Finding duplicate recordsMartin Ramsch20 Aug
    • Re: Finding duplicate recordsBenjamin Pflugmann20 Aug
Re: Finding duplicate recordsAndrey Muratov19 Aug
Re: Finding duplicate recordsAndrey Muratov19 Aug