Can you apply a WHERE clause to a computed column

Contributor ,
Aug 22, 2020 Aug 22, 2020

Copied

I have a MySQL query that returns locations based on a computed column. I can apply a limit clause to the results, but I can't figure out how to apply a WHERE clause. The computed column is an integer, and I'd like the results to be less than a given number from the distance column, as in:

SELECT
MID,
AptName,
City,
State,
Zip,
ROUND((ACOS((SIN(#latitude#/57.29577951) * SIN(lat/57.29577951)) +
(COS(#latitude#/57.29577951) * COS(lat/57.29577951) *
COS(lon/57.29577951 - #longitude#/57.29577951)))) * 3963,2)
AS distance
FROM
tbl_maps
WHERE distance < 5.00 (the query runs fine without this line)
ORDER BY
distance ASC;

Any help appreciated.

John

TOPICS

Views

127

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Contributor , Aug 22, 2020 Aug 22, 2020
I jiggered with it and arrived at a solution. Perhaps someone can describe one more elegant. I cast the WHERE clause as the calculation: WHERE  ROUND((ACOS((SIN(#latitude#/57.29577951) * SIN(lat/57.29577951)) + (COS(#latitude#/57.29577951) * COS(lat/57.29577951) * COS(lon/57.29577951 - #longitude#/57.29577951)))) * 3963,2) <=  (the desired distance)

Likes

Participant , Aug 23, 2020 Aug 23, 2020
You can use a sub query to avoid defining the calculation multiple times. SELECT  MID, AptName, Address, City, State, Zip,distancefrom (  SELECT    MID,   AptName,   Address,   City,   State,   Zip,   ROUND((ACOS((SIN(#latitude#/57.29577951) * SIN(lat/57.29577951)) +   (COS(#latitude#/57.29577951) * COS(lat/57.29577951) *   COS(lon/57.29577951 - #longitude#/57.29577951)))) * 3963,2)   AS distance   FROM   tbl_maps) as x WHERE distance < 5.00 ORDER BY distance ASC;

Likes

3 Replies 3
Contributor ,
Aug 22, 2020 Aug 22, 2020

Copied

I jiggered with it and arrived at a solution. Perhaps someone can describe one more elegant. I cast the WHERE clause as the calculation:
WHERE
ROUND((ACOS((SIN(#latitude#/57.29577951) * SIN(lat/57.29577951)) +
(COS(#latitude#/57.29577951) * COS(lat/57.29577951) *
COS(lon/57.29577951 - #longitude#/57.29577951)))) * 3963,2)
<=  (the desired distance)

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Participant ,
Aug 23, 2020 Aug 23, 2020

Copied

You can use a sub query to avoid defining the calculation multiple times.

SELECT
MID,
AptName,
City,
State,
Zip,

distance

from (

SELECT
MID,
AptName,
City,
State,
Zip,
ROUND((ACOS((SIN(#latitude#/57.29577951) * SIN(lat/57.29577951)) +
(COS(#latitude#/57.29577951) * COS(lat/57.29577951) *
COS(lon/57.29577951 - #longitude#/57.29577951)))) * 3963,2)
AS distance
FROM
tbl_maps

) as x
WHERE distance < 5.00
ORDER BY
distance ASC;

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Contributor ,
Aug 24, 2020 Aug 24, 2020

Copied

LATEST

Indeed, that's more elegant. I know enough about SQL to get by most days, but not enough to always use it well. Thanks!

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more