CODEWITHSUNDEEP
sql
Richi
Richi

Reputation: 546

column "distance" does not exist

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;

Upvotes: 0

Views: 48

Answers (2)

Ken White
Ken White

Reputation: 125651

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 
WHERE distance < 25 ORDER BY distance;

Upvotes: 2

Hendra Sirait
Hendra Sirait

Reputation: 57

you need to put CTA to order it by distance

Upvotes: 0

Related Questions