/*
試した結果
①left joinは誤判定、inner joinにする
 ただし直積はカンマでもCROSS JOINでもOK
②同じ結果でも、距離を|X1-X0|+|Y1-Y0|で評価すると誤判定
 きっちり √ までつけなくていいが、２点間の距離の公式を使う
*/

--清書版
WITH base_point AS (
    --基準地点
    SELECT
        LATITUDE AS base_LA
        ,LONGITUDE AS base_LO
    FROM
        LOCATION_TBL
    WHERE
        DISTRICT_CODE = '1101'
    ),
    
    distance_list AS (
    --各地点の距離を算出
    SELECT distinct
        l.DISTRICT_CODE AS CODE
        ,d.DISTRICT_NAME AS NAME
        ,l.LATITUDE AS LAT
        ,l.LONGITUDE AS LON
        ,POW((l.LATITUDE - base_LA),2) + pow((l.LONGITUDE - base_LO),2) AS distance
    FROM
        LOCATION_TBL l
        ,base_point
        INNER JOIN DISTRICT d
        ON l.DISTRICT_CODE = d.DISTRICT_CODE
    WHERE
        l.DISTRICT_CODE <> '1101'
    )

SELECT
    CODE
    ,NAME
    ,LAT
    ,LON
FROM
    distance_list
ORDER BY
        distance DESC
        ,CODE ASC
;