CODEWITHSUNDEEP
mysql
rblythe
rblythe

Reputation: 103

MySQL select to find similar lat/lng with matching name column

I am trying to find rows in a single table of locations that have the same latitude/longitude when rounded to 2 decimal places as well as the same name. Here is my table (for example):

+---------------------------------------+
| ID | lat     |  lng   |  name         |
+---------------------------------------+ 
| 11 | -11.119 | 13.891 | Smith's Place |
| 81 | -11.121 | 13.893 | Smith's Place |
+---------------------------------------+

What SELECT statement would find instances (like the one above) where the lat/lng match when rounded to 2 decimal places...and the names are the same?

I am looking for something similar to this query that obviously doesn't work (but is asking for what I am after):

SELECT * FROM pb_locations GROUP BY ROUND(lat,2),ROUND(lng,2) WHERE name = name HAVING count(ID) > 1

Upvotes: 0

Views: 267

Answers (1)

Barmar
Barmar

Reputation: 781300

WHERE name = name is always true, since it's just comparing within the same row, not across different rows.

You need to put all 3 columns in the GROUP BY clause.

SELECT *
FROM pb_locations
GROUP BY ROUND(lat, 2), ROUND(lng, 2), name
HAVING COUNT(*) > 1

Upvotes: 1

Related Questions