(Again) leecode - 612. Shortest Distance in a Plane

난이도 : Medium

 

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.

 

 

SELECT ROUND(SQRT(MIN((p2.x - p1.x) * (p2.x - p1.x) + (p2.y - p1.y) * (p2.y - p1.y))), 2) shortest
FROM Point2D p1
    ,Point2D p2
WHERE p1.x <> p2.x 
OR p1.y <> p2.y

Accepted (65.93%)

 

 

WITH sub AS (
             SELECT ROUND(SQRT((p2.x - p1.x) * (p2.x - p1.x) + (p2.y - p1.y) * (p2.y - p1.y)), 2) shortest
                  , DENSE_RANK() OVER (ORDER BY ROUND(SQRT((p2.x - p1.x) * (p2.x - p1.x) + (p2.y - p1.y) * (p2.y - p1.y)), 2)) rnk
             FROM Point2D p1
                 ,Point2D p2
             WHERE (p1.x <> p2.x OR p1.y <> p2.y)
             )
    
SELECT DISTINCT shortest
FROM sub
WHERE rnk = 1

Accepted (17.50%)