LeetCode 612. Shortest Distance in a Plane SQL Solution

Problem

LeetCode SQL Problem

612. Shortest Distance in a Plane

point_2d table

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

Solution

MySQL

Shortest Distance in a Plane

-- Self Join the table to get a combination of different points to show up in the same row
-- Use the following formula to calculate distance between 2 points
-- d = √((x2 - x1)^2 + (y2 - y1)^2), where (x1, y1) and (x2, y2) are the x and y coordinates of two points.
SELECT min(cast(SQRT(power(P2.x - P1.x, 2) + power(P2.y - P1.y, 2)) AS DECIMAL(10, 2))) AS shortest
FROM point_2d AS P1
INNER JOIN point_2d AS P2 ON NOT (
		P1.x = P2.x
		AND P1.y = P2.y
		)

TSQL

Shortest Distance in a Plane

-- Self Join the table to get a combination of different points to show up in the same row
-- Use the following formula to calculate distance between 2 points
-- d = √((x2 - x1)^2 + (y2 - y1)^2), where (x1, y1) and (x2, y2) are the x and y coordinates of two points.
SELECT min(cast(SQRT(power(P2.x - P1.x, 2) + power(P2.y - P1.y, 2)) AS DECIMAL(10, 2))) AS shortest
FROM point_2d AS P1
INNER JOIN point_2d AS P2 ON NOT (
		P1.x = P2.x
		AND P1.y = P2.y
		)

Query Output

shortest
1.00