-- *** Test Data ***
CREATE TABLE #t
(
	DistNo int NOt NULL
	,sponsor int NOt NULL
	,[rank] int NOt NULL
);
INSERT INTO #t
VALUES (1, 1, 50), (2, 1, 20), (3, 1, 20), (4, 1, 30), (5, 3, 30)
	,(6, 3, 50), (7, 5, 20), (8, 6, 20), (9, 6, 40), (10, 6, 20);
-- *** End Test Data ***

WITH Generations
AS
(
	SELECT ROW_NUMBER() OVER (ORDER BY DistNo) AS Generation
		,DistNo, sponsor, [rank]
	FROM #t
	WHERE [rank] >= (SELECT [rank] FROM #t WHERE DistNo = 1)

	UNION ALL

	SELECT G.Generation, T.DistNo, T.sponsor, T.[rank]
	FROM #t T
		JOIN Generations G
			ON T.Sponsor = G.DistNo
	WHERE T.DistNo <> T.sponsor
		AND T.[rank] < (SELECT [rank] FROM #t WHERE DistNo = 1)

)
SELECT Generation, DistNo, sponsor, [rank]
FROM Generations
ORDER BY Generation, DistNo;