Eliminate subquery for average numeric value
Posted
by Dave Jarvis
on Stack Overflow
See other posts from Stack Overflow
or by Dave Jarvis
Published on 2010-05-06T06:17:22Z
Indexed on
2010/05/06
6:28 UTC
Read the original article
Hit count: 246
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!
© Stack Overflow or respective owner