0

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)?

Micky_mike
  • 23
  • 3

1 Answers1

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 );
Calvin Froedge
  • 538
  • 1
  • 6
  • 16
  • As long as you're not expecting more than a few dozen duplicates, this should work fine. You could write a similar query to delete the duplicates as well. – Calvin Froedge Jan 30 '11 at 19:05