Aggregate function with MIN (), GROUP BY, and Subquery

Question

Aggregate function with MIN (), GROUP BY, and Subquery

Navigation

#1 by (1 votes)

1

I'm having trouble building a query from an address table with latitude and longitude.

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

In the table you have some important fields that you would like to return. Are they: id, user_id, lat, lng

My query looks like this:

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

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

The big problem is that a user can have more than one address , so my need is to bring one a user has more than one address, would like to take the 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;

mysql

asked by anonymous 22.02.2018 / 21:19

1 answer

proportionally resizing image Post by postman is giving Object reference error not set to an instance of an object

score 1 · Accepted Answer

Try to do the following:

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)

Assuming that the same user does not have different ID records with the same latitude and longitude. And it is expected to result in different ID's with different latitude and longitude, but with the same distance.