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

Filed under:
|
|

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

Related posts about sql

Related posts about mysql