Mysql use 2 functions in 1 query

I’m trying to use 2 same functions in 1 query which is this

    select *,  ST_Distance_Sphere( point(
                31.00000, 35.00000),
                point(latitude_0, longitude_0)) * .0001
                as `distance_in_km` ,

          ST_Distance_Sphere( point (
                31.00000, 35.00000),
                point(dest_latitude_0, dest_longitude_0)) * .0001
                as `distance_in_km`

               from `most_places_used` 
                having `distance_in_km` <= '0.001' 
order by `distance_in_km` asc Limit 1

This uses only the first function and give right result for the first latlngs only.

I need the row which is almost near to the 2 latlngs (the latlngs is just example don’t care if it’s wrong <3 ) I want it to Give me the row that equals the functions suppose to be the functions latlngs near for the 4 column latlngs

This is the column names.. as in the function ST_Distance_Sphere

(latitude_0, longitude_0, dest_latitude_0, dest_longitude_0)

I need something similar to this query do the same need or the fix for it <3

Answer

You are giving the same alias to two different columns, which makes your query ambiguous about which one you want to use to filter and order. I would recommend two different aliases and least() instead:

select *
from (
    select 
        mpu.*,  
        ST_Distance_Sphere( 
            point(31.00000, 35.00000),
            point(latitude_0, longitude_0)
        ) * 0.0001 as distance_in_km1,
        ST_Distance_Sphere( 
            point (31.00000, 35.00000),
            point(dest_latitude_0, dest_longitude_0)
        ) * 0.0001 as distance_in_km2
    from most_places_used mpu
) t
where least (distance_in_km1, distance_in_km2) <= 0.001
order by least (distance_in_km1, distance_in_km2)
limit 1

Note that I used a subquery rather than a having clause: this is a more standard and readable way to proceed, for no performance penalty anyway.

If you don’t need to differentiate the two computations in the resultset, you can move least() directly to the subquery:

select *
from (
    select 
        mpu.*,  
        least(
            ST_Distance_Sphere( 
                point(31.00000, 35.00000),
                point(latitude_0, longitude_0)
            ),
            ST_Distance_Sphere( 
                point (31.00000, 35.00000),
                point(dest_latitude_0, dest_longitude_0)
            )
        ) * 0.0001 as distance_in_km
    from most_places_used mpu
) t
where distance_in_km <= 0.001
order by distance_in_km
limit 1