ORDER BY not working while trying to get distance by ascending order

Question

select * from `users` 
where exists (
    select *, (6371 * acos(cos(radians(22.559648)) * cos(radians(`lat`)) * cos(radians(`lng`) - radians(88.415552)) + sin(radians(22.559648)) * sin(radians(`lat`)))) AS distances 
    from `locations` 
    where `users`.`location_id` = `locations`.`id` 
    and (6371 * acos(cos(radians(22.559648)) * cos(radians(`lat`)) * cos(radians(`lng`) - radians(88.415552)) + sin(radians(22.559648)) * sin(radians(`lat`)))) < 32.688888 
    ORDER BY `distances` DESC)

Answer 1

You need to put ORDER BY in the main query. The subquery is just being used to tell if a row exists, the order doesn't matter.

But distances isn't a column in the main query, so you can't order by it with the query you wrote. Instead of using EXISTS, you need to join with the subquery.

SELECT u.*, (6371 * acos(cos(radians(22.559648)) * cos(radians(`lat`)) * cos(radians(`lng`) - radians(88.415552)) + sin(radians(22.559648)) * sin(radians(`lat`)))) AS distances 
FROM users AS u
JOIN locations AS l ON `u`.`location_id` = `l`.`id` 
HAVING distances < 32.688888 
ORDER BY distances DESC