Shortest Distance in a Line
Problem
Point(x). Return the shortest distance between any two distinct points on the x-axis.
Input
p=[(-1,),(0,),(2,)]Output
1Sorted: -1, 0, 2. Shortest gap is 0-(-1)=1.
SELECT MIN(p2.x - p1.x) AS shortest
FROM Point p1 JOIN Point p2 ON p1.x < p2.x;Explanation
We want the smallest gap between any two distinct points on a line. The neat SQL trick is to self-join the table to itself so every pair of points appears together, then take the minimum distance.
The join condition p1.x < p2.x does two jobs at once: it pairs each point with a different point, and it guarantees p2.x - p1.x is always positive, so we don't need an absolute value.
Then MIN(p2.x - p1.x) simply picks the smallest of all those positive gaps.
Example: points -1, 0, 2. The pairs give gaps 0-(-1)=1, 2-(-1)=3, and 2-0=2. The smallest is 1.