loops - Walking a tree backwards in SQL -

i have table simple schema:

create table q(  orig integer not null,  dest integer not null,  cost  float,  primary key (orig, dest) ); 

i need walk table backwards in cost minimizing way. let me explain.

i need tour of 15 points, point can orig or dest. algorithm backtracking last dest initial orig. here how spell out:

given final dest, find orig link said dest minimum cost. corresponding orig becomes new dest; loop on 15 times.

let's assume know last dest number 10. sql statement allows me find orig leading dest in cost-minimizing way is:

select orig q cost = (select min(cost) q dest = 10); 

now use orig returned above function find previous point (assume returned, say, point 5):

select orig q cost = (select min(cost) q dest = 5); 

i can go on until have 15 points.

how make efficient query in sql? table has 50 million rows.

here's query assumes you're using sql server 2005+. uses common table expressions (cte). example returns paths, cumulative cost, have selected orig , dest. shows number of points in path. altered return best choice (e.g, lowest cost, fewest steps, etc.)

i don't know how performance be. but, indexes helpful.

with paths  -- list of paths     ( select row_number() on (order orig) pathnumber, orig,         dest, cost, 1 points     q      union      select paths.pathnumber, paths.orig,         q.dest, q.cost, paths.points + 1 points     paths     join q on paths.dest = q.orig     paths.points < 15     )     , pathsrows  -- total points in each path     ( select count(*) on (partition pathnumber) totalpoints, pathnumber         , orig, dest, cost, points     paths     )     , pathssum  -- summarize each path     ( select pathnumber,         min(case when points = totalpoints orig end) orig,         max(case when points = totalpoints dest end) dest,         sum(cost) cost, max(points) points     pathsrows     group pathnumber     )  select pathnumber, orig, dest, cost, points pathssum dest = 4     , orig = 1 order pathnumber, points