Mysql group by conflict records with order by

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 1

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) AS 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
ORDER BY min_dealer_distance ASC