613_Shortest Distance in a Line

[easy]

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.

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

| x   |
|-----|
| -1  |
| 0   |
| 2   |

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

| shortest|
|---------|
| 1       |

Solution:

SELECt min(abs(p1.x - p2.x)) AS shortest
FROM point p1 
    JOIN point p2 ON p1.x < p2.x;

Follow-up: What if all these points have an id and are arranged from the left most to the right most of x axis?

In this case, we only need to check the distance between conjoint points.

SELECt min(abs(p1.x - p2.x)) AS shortest
FROM point p1 
    JOIN point p2 ON p2.id - p1.id = 1;