51

I have a table which has 2 fields (latitude, longitude) and many other fields. I want to select the distinct combinations of latitude and longitude from this table.

What would be the query for that?

informatik01
  • 16,038
  • 10
  • 74
  • 104
Codevalley
  • 4,593
  • 7
  • 42
  • 56

5 Answers5

84

Simply use the DISTINCT keyword:

SELECT DISTINCT Latitude, Longitude 
FROM Coordinates;

This will return values where the (Latitude, Longitude) combination is unique.

This example supposes that you do not need the other columns. If you do need them, i.e. the table has Latitude, Longitude, LocationName columns, you could either add LocationName to the distinct list, or use something along the lines of:

SELECT Latitude, Longitude, MIN(LocationName)
FROM Coordinates
GROUP BY Latitude, Longitude;
informatik01
  • 16,038
  • 10
  • 74
  • 104
SWeko
  • 30,434
  • 10
  • 71
  • 106
  • For me two query returns different number of result. For example first query returns 1000 rows and second query (first query + GROUP BY clause) returns 800 rows. – ratulalahy Oct 29 '16 at 03:32
  • The second query get only one (more or less random) LocationName, if you have more than one. – SWeko Oct 31 '16 at 12:09
  • 4
    Oh, I used to think "distinct" applies only to the column right next to it. So "select distinct" is a different statement than "select", "distinct" is not a modifier/operation on a column! – masterxilo Feb 06 '19 at 12:40
13

its an old post. but I just came across it while looking for an anser for the same problem. The above answer didn't work for me, but I found another simple solution using CONCAT():

SELECT *
FROM Coordinates
GROUP BY CONCAT(Latitude, Longitude);

This will give you all the unique Latitude / Longitude combinations, without any limitations to the select part of the query.

Dennis van Schaik
  • 464
  • 5
  • 19
  • for some reason concat doesn't work for me, it causes an error in the query and when I type the query in http://127.0.0.1:8080/phpmyadmin, having installed wamp, it doesn't even pop up as an option http://i.imgur.com/Egqpwct.png – barlop Oct 23 '16 at 01:17
  • This is by far the most flexible solution; unfortunately, this answer relies on a partial `GROUP BY` clause. Users who are unable to ensure that the `ONLY_FULL_GROUP_BY` MySQL mode is disabled at query time _or_ are using a managed service that doesn't allow for this mode to be changed (e.g. CloudSQL as of May '20) are unable to take advantage of this solution. – Mike May 27 '20 at 16:58
  • Cannot group on fields selected with '*'. – NehaK Mar 16 '21 at 18:01
2

I think it will be something about :

SELECT latitude, longitude 
FROM table_name t1 
INNER JOIN table_name t2 
WHERE t1.latitude <> t2.latitude OR t1.longitude <> t2.longitude

That is the SELF INNER JOIN.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
Bax
  • 4,260
  • 5
  • 43
  • 65
1
SELECT COLUMN1,COLUMN2 
FROM TABLE_NAME 
GROUP BY COLUMN1,COLUMN2 -- This will fetch the unique combinations

-- During this kind of selection it is always better to give a where condition so that the results are filtered before grouping

Your Code:

SELECT Latitude, Longitude 
  FROM Coordinates
GROUP BY Latitude, Longitude
Bala
  • 111
  • 8
-2
SELECT DISTINCT BY ( latitude, longitude)

This will work.

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Shivansh
  • 1