Shortest distance in line on x-axis

Points of x coordinates are stored in table “points“. All entries of these points in table are integers and unique. Write a query to find shortest distance between two points on x-axis.

---

 

---

Explanation:

All possible combinations of all points in input table and differences are as follows :

SELECT
    p1.x AS x1,
    p2.x AS x2,
    ABS(p1.x - p2.x) AS difference
FROM
points p1
JOIN
points p2 
ON 
p1.x != p2.x
ORDER BY
p1.x , p2.x
;

 

x1	x2	difference
-6	-3	3
-6	0	6
-6	10	16
-3	-6	3
-3	0	3
-3	10	13
0	-6	6
0	-3	3
0	10	10
10	-6	16
10	-3	13
10	0	10

From all differences as shown in above table, minimum difference is 3 and so, is output.

---

DDLs:

CREATE TABLE IF NOT EXISTS points ( x INT NOT NULL );
INSERT INTO points VALUES (-3);
INSERT INTO points VALUES (-6);
INSERT INTO points VALUES (0);
INSERT INTO points VALUES (10);

 

---

Solution:

SELECT
    MIN(ABS(p1.x - p2.x)) AS shortest
FROM
    points p1
        JOIN
    points p2 
ON p1.x != p2.x
;