SQL. Query for aggregating certain dependent fields.

Question

Sorry for the vague title, I didn't know how else to phrase this.

Suppose you have a table such as the following table of stops on a train and the distances between this stop and the next:

Stop         NextStop       Distance
------------------------------------
Middletown   Bloomsbury     101
Bloomsbury   Shanksville    36
Shanksville  New City       53
New City     Washington     339
Washington   Andover        48

The problem is to determine the distance between Stops X and Y. So, for example, the distance between Middletown and Shanksville is 101+36=137.

Is there a way to do this with a single query? (Ideally, I am looking for a way to iteratively go through the Stops column by using the NextStop field of the previous row as the key the next row, and then collecting their distances into a column.)

Thanks!

Answer 1

In Oracle, I think something like this might work:

Select sum(distance)
from train_stops
start with stop = 'Middletown'
connect by stop = prior  nextstop;