Solving https://www.brentozar.com/archive/2025/06/query-exercise-return-routes-in-the-right-order/

station exercise

DROP TABLE IF EXISTS dbo.Stations;
CREATE TABLE dbo.Stations
(
	StationId			 INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	StationName			 VARCHAR(50),
	StationPhysicalOrder INT
);

INSERT INTO dbo.Stations (StationName, StationPhysicalOrder)
SELECT	CHAR(64 + Numbers.n),
		Numbers.n
FROM
		(
			VALUES (1),
				   (2),
				   (3),
				   (4),
				   (5),
				   (6),
				   (7),
				   (8),
				   (9),
				   (10),
				   (11),
				   (12),
				   (13),
				   (14),
				   (15),
				   (16),
				   (17),
				   (18),
				   (19)
		) AS Numbers (n);

SELECT	*
FROM	dbo.Stations;


DROP TABLE IF EXISTS dbo.StationRoutingOverride;
CREATE TABLE dbo.StationRoutingOverride
(
	StationRoutingOverrideId INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
	StationFromName			 VARCHAR(50),
	StationToName			 VARCHAR(50)
);

INSERT INTO dbo.StationRoutingOverride (StationFromName, StationToName)
VALUES
('E', 'S'),
('B', 'I'),
('I', 'D');

SELECT	* 
FROM	StationRoutingOverride;



WITH NextStation AS
(
	SELECT		Stations.StationName	AS FromStationName,
				NextStation.StationName AS NextStationName
	FROM		Stations
	LEFT JOIN	StationRoutingOverride
		   ON	StationRoutingOverride.StationFromName = Stations.StationName

   LEFT JOIN	Stations AS NextStation
		   ON	(
					StationRoutingOverride.StationToName IS NOT NULL
			  AND	StationRoutingOverride.StationToName = NextStation.StationName
				)
		   OR
				(
					StationRoutingOverride.StationRoutingOverrideId IS NULL
			  AND	NextStation.StationId = Stations.StationId + 1
				)
),
	 uglyStations AS
(
	SELECT	CAST(null AS VARCHAR(50)) AS FromStationName,
			Stations.StationName AS NextStationName
	FROM	Stations
	WHERE	Stations.StationName = 'A'
	UNION ALL
	SELECT		NextStation.FromStationName,
				NextStation.NextStationName
	FROM		NextStation
   INNER JOIN	uglyStations
		   ON	uglyStations.NextStationName = NextStation.FromStationName
)
SELECT	uglyStations.FromStationName
FROM	uglyStations
WHERE	uglyStations.FromStationName IS NOT NULL