How to search MySQL table for duplicates

Question

In MySQL, I have a table named "city" with "city_name" and "latitude" and "longitude" in it. It appears that I have a few duplicates. What query can I run (in phpmyadmin) that will search through the "city" table for entries containing the same latitude and longitude (with different "city_name"'s)?

Answer 1

Run the query in mysql directly. First access mysql from command line:

sudo mysql -p

Then select the database you want to use

show databases;
use databaseiwant;

Now run a query on the table you need to find duplicates in

show tables;
SELECT fieldtocheckduplicates, COUNT(fieldtocheckduplicates) AS NumOccurrences FROM tabletocheck GROUP BY fieldtocheckduplicates HAVING ( COUNT(fieldtocheckduplicates) > 1 );