We cannot use ORDER BY clauses in common table expression directly in sql server. For example:
SELECT * FROM Student ORDER BY 1
)UPDATE cteStudent SET Stu_Name = 'Scott'
We will get error message like :
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.
We must have to use either TOP or FOR XML. For example:
SELECT TOP(1) * FROM Student ORDER BY 1