column "distance" does not exist-CodePudding

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 up to the FROM posts, it returns the post id and the distance column.

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;

CodePudding user response：

You can't use a column alias in other places in the same query. You'll have to either repeat the entire expression in your HAVING and ORDER BY or use your existing query as a sub-query and apply the HAVING and ORDER BY to the outer query, or use a CTE if your RDBMS supports it.

SELECT id, distance 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) p 
HAVING distance < 25 ORDER BY distance;

CodePudding user response：

you need to put CTA to order it by distance