Assuming a table definition:
Code:
CREATE TABLE Distances
(City varchar(20) not null Primary Key,
Distance integer not null);
Then the query would be:
Code:
SELECT City, Distance -
COALESCE((SELECT MAX(distance) FROM Distances D2
WHERE D2.Distance < D1.Distance), 0) as CityDifference
FROM Distances D1
ORDER BY Distance;
The idea is that the correlated subquery finds the maximum distance for all the cities whose distance value is less than the current row. This would be the city just 'before' the current one. Subtracting this maximum value from the current distance value gives the distance from the prior city.
The COALESCE is in there to protect the subtraction for the first city (LA). Because there are no cities 'less than' LA, the subquery would return null, and thus LA's distance minus a null gives null. I assume you really want LA's distance as the first entry and the COALESCE provides that.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Very tricky challenge
Linear Mode
