Question

Formatted question description: https://leetcode.ca/all/612.html

 612. Shortest Distance in a Plane

 Table point_2d holds the coordinates (x,y) of some unique points (more than two) in a plane.

 Write a query to find the shortest distance between these points rounded to 2 decimals.

 | x  | y  |
 |----|----|
 | -1 | -1 |
 | 0  | 0  |
 | -1 | -2 |

 The shortest distance is 1.00 from point (-1,-1) to (-1,2). So the output should be:

 | shortest |
 |----------|
 | 1.00     |

 Note: The longest distance among all the points are less than 10000.

Algorithm

CROSS JOIN finds the distance between the current point and each point;
Exclude self-connected records (distance is 0);
Take min, take two decimal places

CROSS JOIN: https://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql

Code

SQL

SELECT
    ROUND(
        SQRT(
            MIN(POW(a.x-b.x,2) + POW(a.y-b.y,2))
        )
        ,2
    ) shortest
FROM point_2d a 
CROSS JOIN point_2d b
WHERE NOT (a.x = b.x AND a.y = b.y)
-- where (a.x != b.x and a.y != b.y) -- this will not work, it will remove all pairs where x is the same and y is the same

612 - Shortest Distance in a Plane

Question

Algorithm

Code

All Problems

All Solutions