Welcome to Subscribe On Youtube

Question

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

Table: Point2D

+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
+-------------+------+
(x, y) is the primary key column for this table.
Each row of this table indicates the position of a point on the X-Y plane.

 

The distance between two points p1(x1, y1) and p2(x2, y2) is sqrt((x2 - x1)2 + (y2 - y1)2).

Write an SQL query to report the shortest distance between any two points from the Point2D table. Round the distance to two decimal points.

The query result format is in the following example.

 

Example 1:

Input: 
Point2D table:
+----+----+
| x  | y  |
+----+----+
| -1 | -1 |
| 0  | 0  |
| -1 | -2 |
+----+----+
Output: 
+----------+
| shortest |
+----------+
| 1.00     |
+----------+
Explanation: The shortest distance is 1.00 from point (-1, -1) to (-1, 2).

Algorithm

  1. CROSS JOIN finds the distance between the current point and each point;
  2. Exclude self-connected records (distance is 0);
  3. 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

All Problems

All Solutions