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