You're filtering your inner query by the outer query values. That will affect the results of the inner query and thus the row numbers. To ensure consistent row numbers, do not use any dependencies external to the query where the row numbers are generated.

WITH cte AS

( SELECT p2.ID,

rowNum = ROW_NUMBER() OVER (PARTITION BY p2.DomainID,

p2.SchoolID

ORDER BY p2.ID

)

FROM #Project AS p2

)

SELECT p1.ID,

ProjectWithSchoolAndDomain = #Domain.Name + ' - ' + #School.Name + ' - ' + CAST(p2.rowNum AS VARCHAR)

FROM #Project AS p1

JOIN #Domain

ON p1.DomainID = #Domain.ID

JOIN #School

ON p1.SchoolID = #School.ID

JOIN cte AS p2

ON p1.ID = p2.ID;

Update: On further inspection, it looks like your query will work as written because your row_number partitioning and ordering are self-contained in the inner query and your filtering is on the same columns as your partitioning.

I would still be hesitant to use the reference to an external resource in general because if the rows are eliminated before row_numbers are assigned, you'll get different results. Although it works, you'll need to be careful if you modify it in the future or re-use the strategy elsewhere.