sql notes: leetcode#613 shortest distance in a line

Problem

Table point holds the x coordinate of some points on x-axis in a plane, which are all integers.

Write a query to find the shortest distance between two points in these points.

The shortest distance is ‘1’ obviously, which is from point ‘-1’ to ‘0’. So the output is as below:

Note: Every point is unique, which means there is no duplicates in table point.

Analysis

We can cross join point table with itself to generate all possible pairs of points. Then calculate distaneces and select minimum value:

1

SELECT MIN(ABS(p2.x - p1.x)) AS shortest FROM point AS p1, point AS p2 WHERE p1.x <> p2.x;

The above query calculates each distance twice and we can assume the first point is smaller than the second:

1

SELECT MIN(p2.x - p1.x) AS shortest FROM point AS p1, point AS p2 WHERE p1.x < p2.x;

Solution

Solution 1

1

SELECT MIN(ABS(p2.x - p1.x)) AS shortest FROM point AS p1, point AS p2 WHERE p1.x <> p2.x;

Solution 2

1

SELECT MIN(p2.x - p1.x) AS shortest FROM point AS p1, point AS p2 WHERE p1.x < p2.x;

Link

613. Shortest Distance in a Line
(中文版) SQL 笔记: Leetcode#613 Shortest Distance in a Line