Eliminate subquery for average numeric value
- by Dave Jarvis
Quest
A query selects locations that begin with Vancouver, which are in a 5 minute radius from one another.
SQL Code
The following SQL abomination does the trick:
SELECT
NAME
FROM
STATION
WHERE
DISTRICT_ID = '110'
AND NAME LIKE 'Vancouver%'
AND LATITUDE BETWEEN
(SELECT round((min(LATITUDE) + max(LATITUDE)) / 2)-5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%')
and
(SELECT round((min(LATITUDE) + max(LATITUDE)) / 2)+5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%')
AND LONGITUDE BETWEEN
(SELECT round((min(LONGITUDE) + max(LONGITUDE)) / 2)-5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%')
and
(SELECT round((min(LONGITUDE) + max(LONGITUDE)) / 2)+5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%')
ORDER BY
LATITUDE
Question
How can this query be simplified to remove the redundancy, without using a view?
Restrictions
The database is MySQL, but ANSI SQL is always nice.
Thank you!
