able

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.

| 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       |

Note:

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

point

.

Follow-up:

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

solution 1:

SELECT MIN(ABS(p1.x - p2.x)) as shortest

FROM

point p1

JOIN

point p2 ON p1.x != p2.x;

solution 2:

SELECT

ABS(p1.x - p2.x) as distance_measure

FROM

point p1 JOIN point p2

ON p1.x != p2.x

order by distance_measure

limit 1

solution 3:

set @prev := -10000

SELECT min(diff) as shortest

FROM(SELECT (x - @prev) as diff, @prev := x from (SELECT * from point order by x))

Follow-up

set @prev := 10000

SELECT min(diff) as shortest

FROM (select (x - @prev) as diff, @prev := x from point)