user3151197 - 1 year ago 63

MySQL Question

A table contains multiple addresses (latitude and longitude) of a dealers but I want to get nearest dealers

Here is query which return works fine with order by and where dealer=1 of a single dealer but I need multiple unique dealers.

check query and first image result

`SELECT`

*, ROUND(

(

3959 * ACOS(

COS(RADIANS(41.355724)) * COS(

RADIANS(adzip.dealer_zipcode_latitude)

) * COS(

RADIANS(adzip.dealer_zipcode_longitude) - RADIANS(- 87.607332)

) + SIN(RADIANS(41.355724)) * SIN(

RADIANS(adzip.dealer_zipcode_latitude)

)

)

), 2

) AS dealer_distance

FROM

ad_dealers_zipcodes AS adzip

WHERE adzip.dealer_zipcode_dealer_id = 1 #GROUP BY adzip.dealer_zipcode_dealer_id

ORDER BY dealer_distance ASC

when I add group by adzip.dealer_zipcode_dealer_id query returns 4th record but I need the 1st record of result which dealer_distance is 13.80

Answer Source

Actually, I think you should add a level to your query for the group by statement to work as you intend, like this:

```
SELECT *, min(dealer_distance) FROM
(
SELECT
*, ROUND(
(
3959 * ACOS(
COS(RADIANS(41.355724)) * COS(
RADIANS(adzip.dealer_zipcode_latitude)
) * COS(
RADIANS(adzip.dealer_zipcode_longitude) - RADIANS(- 87.607332)
) + SIN(RADIANS(41.355724)) * SIN(
RADIANS(adzip.dealer_zipcode_latitude)
)
)
), 2
) AS dealer_distance
FROM
ad_dealers_zipcodes AS adzip
WHERE adzip.dealer_zipcode_dealer_id = 1 #GROUP BY adzip.dealer_zipcode_dealer_id
ORDER BY dealer_distance ASC
) as t1
GROUP BY dealer_zipcode_dealer_id
```