Usage of ORDER BY in view or single statement (inline) TVF

Views and inline Table-valued functions have no intrinsic order. The order of the result is specified only by the SELECT statement that specifies the view, function, or table.

If executed, this will trigger an error ‘The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified’. 
Some programmers wrongly assume that adding TOP 100 PERCENT is ok because the error disappears. 

You should include the ORDER BY only in the outermost query.

Available in

SQL Prompt

SQL Code Guard