This table has over 20k records. So, I need query to find the duplicates to make my life easy.
We can use INNER JOIN along with logic to find the duplicate.
Query to find the duplicates:
SELECT phones.* FROM phones INNER JOIN(SELECT extension,COUNT(extension) FROM phones GROUP BY extension HAVING COUNT(extension)>1) as phones2 ON phones.extension=phones2.extension ORDER BY phones.extension LIMIT 0,200;
Explanation:
SELECT phones.* FROM phones : This reads all the columns from the table phones
SELECT extension,COUNT(extension) FROM phones GROUP BY extension HAVING COUNT(extension)>1) as phones2 : This reads all the duplicates (i.e extension count > 1) and create a VIEW phones2
ON phones.extension=phones2.extension : This a logic to check duplicate extensions
ORDER BY phones.extension : To sort the output by phones.extension
LIMIT 0,200: To display at most 200 data
References:
http://mysqljoin.com/joins/inner-join-tutorial/
