CODEWITHSUNDEEP
mysqldatabase
El Dude
El Dude

Reputation: 5618

MySQL get minimum from function

I want to get the minimum from a "dynamic" value determined by a function DISTANCE_BETWEEN.

SELECT d.id 
FROM (SELECT id , DISTANCE_BETWEEN (Latitude,Longitude,43.2560997009,-2.9078400135) AS distance FROM Locations) AS D 
WHERE MIN(d.distance);

Can't get it right

Invalid use of group function

Upvotes: 1

Views: 21

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269863

One method is just to move the function to the order by clause:

SELECT D.id 
FROM locations
ORDER BY DISTANCE_BETWEEN(Latitude, Longitude, 43.2560997009, -2.9078400135)
LIMIT 1;

This is the simplest method for expressing the logic (under some circumstances, it might not be the most efficient).

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

Here's another way using order by

SELECT D.id 
FROM (SELECT id , DISTANCE_BETWEEN (Latitude,Longitude,43.2560997009,-2.9078400135) AS distance FROM Locations) AS D
ORDER BY distance
LIMIT 1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

SELECT id
FROM Locations
WHERE DISTANCE_BETWEEN (Latitude,Longitude,43.2560997009,-2.9078400135) =
    (SELECT MIN(DISTANCE_BETWEEN (Latitude,Longitude,43.2560997009,-2.9078400135)) FROM Locations)

Upvotes: 2

Related Questions