1

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!

Jake
  • 15,007
  • 22
  • 70
  • 86

1 Answers1

0

In Oracle, I think something like this might work:

Select sum(distance)
from train_stops
start with stop = 'Middletown'
connect by stop = prior  nextstop;
FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
  • Thanks! Do you happen to know if there is a general SQL solution? – Jake Sep 30 '10 at 17:12
  • @Jake: Not sure. What DBMS are you using? Oracle is not the only one to support recursive queries, but the syntax might vary slightly from to the another. – FrustratedWithFormsDesigner Sep 30 '10 at 17:13
  • I'm not tied to a particular implementation. I mean, I was trying to solve this problem and it just seemed overkill to have multiple queries for each distance segment. Perhaps my single-query approach is not correct. – Jake Sep 30 '10 at 17:19
  • @Jake: For a generic solution, see Portman's answer here: http://stackoverflow.com/questions/53108/is-it-possible-to-make-a-recursive-sql-query – FrustratedWithFormsDesigner Sep 30 '10 at 17:32