MySQL - Select unique values from a table

Question

My MySQL table contains the following information,

ID  User            City            HomeLatitude      HomeLongitude
1   Egon Spengler   New York        19.123456         23.43546
2   Mac Taylor      New York        19.12343588       23.43546
3   Sarah Connor    New York        19.128376         34.35354
4   Jean-Luc Picard La Barre        11.345455         12.4356546
5   Ellen Ripley    Nostromo        32.76865          78.345435

From this table I need to query unique HomeLatitude values with first two decimal point because the first 3 location are almost same, so i need only the first row from the three rows..

I used the below given query,

SELECT DISTINCT HomeLatitude, City FROM user;

But that will return all the rows from the table. Can anyone help me on this.

Possible duplicate of [Format number to 2 decimal places](http://stackoverflow.com/questions/11190668/format-number-to-2-decimal-places) — Gunaseelan, Dec 16 '16 at 09:30

Gunaseelan · Accepted Answer · 2016-12-16T09:40:38.170

6

ROUND Function will give you the number of decimal places that you want to show.

SELECT DISTINCT ROUND(HomeLatitude,2), City FROM user GROUP BY ROUND(HomeLatitude,2);

edited Dec 16 '16 at 09:40

answered Dec 16 '16 at 09:29

Gunaseelan

2,494
5
36
43

But I need only the unique rows after rounding the HomeLatitude values. – Anna Dec 16 '16 at 09:35

Pieter21 · Answer 2 · 2016-12-16T09:37:14.890

2

Something like:

   SELECT (DISTINCT ROUND(HomeLatitude, 1)) AS HomeLatitude, City FROM user;

edited Dec 16 '16 at 09:37

answered Dec 16 '16 at 09:28

Pieter21

1,765
1
10
22

I checked this, It will round HomeLatitude value to 2 decimal point, but the returned rows are not unique. – Anna Dec 16 '16 at 09:32
I changed the opening parenthesis. Otherwise you need the DISTINCT/ROUND as an inner query.. – Pieter21 Dec 16 '16 at 09:37
Sorry, its has some syntax error. I think its not possible to give parenthesis like that. – Anna Dec 16 '16 at 09:40
Ok, I think I mixed in some sqlite. My next suggestion would be to 'group by' but that already is the accepted answer. Good luck! – Pieter21 Dec 16 '16 at 09:53

score 2 · Answer 3 · answered Dec 16 '16 at 09:32

While using a solution that is based on rounding a latitude or longitude may give you results, also be prepared for unexpected results. It is also possible for two places to have the exact same latitude but different longitudes and vice verce.

If you are doing any kind of spatial analysis, you should invest in mysql spatial extensions and a function like ST_WITHIN

Second best option is to use the haversine formula or similar to find places that are close to each other.

MySQL - Select unique values from a table

3 Answers3