Aggregated function with MIN(), GROUP BY and Subquery

Question

I am facing problems to mount a query of an address table with latitude and longitude.

I have a function that does a distance calculation, I called it: distance_latlng_km(originLat, originLng, destLat, destLng).

In the table there are some important fields that I would like to return. They are: id, user_id, lat, lng

My query is like this:

SELECT id, user_id, lat, lng, distance_latlng_km(x, y, lat, lng) as distance FROM addresses;

This returns me correct, listing for me the ID of address, user owner of the address, latitude, longitude and the distance in km resulting from the calculation of latitude and longitude.

The big problem is that a user may have more than one address, then my need is to bring one address per user, if a user has more than one address, would like to shorter distance.

My current query:

SELECT b.id, b.user_id, b.lat, b.lng, distance_latlng_km(x, y, b.lat, b.lng) as distance 
FROM (SELECT user_id, min(distance_latlng_km(x, y, lat, lng)) FROM address GROUP BY user_id) a 
INNER JOIN address b ON b.user_id = a.user_id;

Answer 1

Try doing it this way:

SELECT 
    A.ID, A.USER_ID, A.LAT, A.LNG, DISTANCE_LATLNG_KM(X, Y, A.LAT, A.LNG) AS DISTANCE 
FROM 
    ADDRESS A
WHERE
    DISTANCE_LATLNG_KM(X, Y, A.LAT, A.LNG) = (SELECT MIN(DISTANCE_LATLNG_KM(X, Y, B.LAT, B.LNG)) FROM ADDRESS B WHERE B.USER_ID = A.USER_ID)

This is assuming that the same user does not have different ID records with the same latitude and longitude. And that it is expected as a result different ID’s records with different latitude and longitude, but with the same distance.