Query Exercise: Return Routes in the Right Order

Solution.sql

with t as
(
	select 
		stationName s,
		stationPhysicalOrder o,
		lead(stationName) over (order by stationPhysicalOrder) ns,
		min(stationPhysicalOrder) over () mino
	from
		dbo.Stations
),
r as
(
	select cast(1 as int) o, s from t where o = mino
	union all
	select 
		r.o + 1, isnull(so.ns, t.ns)
	from
		r
		outer apply (select StationToName from dbo.StationRoutingOverride where StationFromName = r.s) so (ns)
		join t on r.s = t.s
)

select s from r 
where s is not null
order by o