Symmetric Pairs [NCTU Floor 27]

# 14) Swap(lat, lon) by ID parity check
UPDATE citytable
    SET lat = lat + lon, lon = lat-lon, lat = lat - lon
    WHERE CASE WHEN 'od' = 'ev' THEN id % 2 = 0
               ELSE id % 2 = 1 END;

# 15) SET lat = lon where ones place of ID is m
UPDATE citytable
    SET lat = lon
    WHERE id % 10 = 6;

# 16) Query kth symmetric pair
#Format : kth = LIMIT k-1, 1
SELECT f1.X, f1.Y
    FROM (SELECT ROUND(lat, 1) X, ROUND(lon, 1) Y
        FROM citytable) f1
            INNER JOIN
         (SELECT ROUND(lat, 1) X, ROUND(lon, 1) Y
        FROM citytable) f2
            ON f1.X=f2.Y AND f1.Y=f2.X
    GROUP BY f1.X, f1.Y
    HAVING COUNT(f1.X) > 1 or f1.X < f1.Y
    ORDER BY f1.X, f1.Y LIMIT 1, 1;