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 ;