Welcome to Subscribe On Youtube

612. Shortest Distance in a Plane

Description

Table: Point2D

+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
+-------------+------+
(x, y) is the primary key column (combination of columns with unique values) 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 a solution to report the shortest distance between any two points from the Point2D table. Round the distance to two decimal points.

The 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).

Solutions

• SQL

• # Write your MySQL query statement below
  
  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
  
  --
  
  SELECT ROUND(SQRT(POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)), 2) AS shortest
  FROM
      Point2D AS p1
      JOIN Point2D AS p2 ON p1.x != p2.x OR p1.y != p2.y
  ORDER BY 1
  LIMIT 1;
  
  

All Problems

All Solutions