Compare closest value in MySQL

Question

I'm trying to compare two values against the values in my table to find the closest one, but each time I try the code (see below) I just get the first row returned

    SELECT latitude, longitude, ABS(latitude - 53.316494), ABS(longitude - -6.253541) AS distance
    FROM station
    ORDER BY distance
    LIMIT 1;

Thanks for your help.

How do you define closest?. This you should define as a computed var and order in your select ascending. — sqlab, Apr 24 '14 at 13:08
this will return 4 value of the first line as you just ask select and no Where condition. maybe with betwin? — ssbb, Apr 24 '14 at 13:09
This might help: http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula — John Bupit, Apr 24 '14 at 13:33

score 0 · Accepted Answer · answered Apr 24 '14 at 13:40

Your query:

SELECT latitude, longitude, ABS(latitude - 53.316494), ABS(longitude - -6.253541)
FROM station
LIMIT 1;

But "Compare closest value in MySQL" in latitude and longitude means to find closest way Should looks like:

SELECT latitude, longitude, ABS(latitude - 53.316494), ABS(longitude - -6.253541)
FROM station
ORDER BY (POW((latitude - 53.316494,2) + POW((longitude - -6.253541),2)) ASC
LIMIT 1;

enter image description here

Compare closest value in MySQL

1 Answers1