Home / Questions / 563459
Accepted
ricardo leiva sikic
Asked: 2022-10-21 17:51:19 +0800 CST 2022-10-21 17:51:19 +0800 CST

column "distance" does not exist

  • 772

I am trying to run a query that finds the places near my location, but every time I try to run it, it returns the following error: column "distance" does not exist If I remove distance and only leave until FROM postsit returns the post id and a column distance.

But if I leave it in the original way, it returns the error.

SELECT id, ( 3959 * acos( cos( radians(-32.63) ) * cos( radians( latitude ) ) * 
cos( radians( longitude ) - radians(-71.42) ) + sin( radians(-32.63) ) * 
sin( radians( latitude ) ) ) ) AS distance FROM posts HAVING
distance < 25 ORDER BY distance;
sql

1 Answers

  1. Best Answer

    This is happening because aliased columns can be used in an order by but they cannot be evaluated by a where, group by or having you can see it in the sql server documentation sql server documentation

    A possible solution could be this:

    SELECT * 
FROM ( SELECT 
      id, 
      ( 3959 * acos( cos( radians(-32.63) ) * cos( radians( latitude ) ) * 
      cos( radians( longitude ) - radians(-71.42) ) + sin( radians(-32.63) ) * 
      sin( radians( latitude ) ) ) ) AS distance 
  FROM posts 
  ORDER BY distance ) as TempPost
WHERE distance < 25

    Be careful because this code thus becomes a bit more complicated to read

    • 1