Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

Absolutely not. Proof:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC),
   ROW_NUMBER() OVER(ORDER BY A.[Name] DESC)
FROM
   [FooTable] AS A

The only way to guarantee an order in SQL is to ask for it, use ORDER BY on the result itself.

If you had asked the question I think you actually meant to ask:

How can I order by ROW_NUMBER() without repeating the complex ORDER BY expression?

We could have told you to create an alias for the ROW_NUMBER() expression, and then sort using the alias:

SELECT
   A.[Name],
   rn = ROW_NUMBER() OVER (ORDER BY <complex expression>)
FROM
   dbo.[FooTable] AS A
ORDER BY rn;

Now you only have to change the expression in one place, and the resulting sort will be based on that expression without having to repeat it.

Related