user3151197 user3151197 - 4 months ago 11
MySQL Question

Mysql group by conflict records with order by

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


enter awfawefawefa description here
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

enter image description here

Answer

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
Comments