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.


 

Input Table: points

x-axis
-3
-6
0
10

Output Table:

shortest
3

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
;

Leave a Comment