ORDER BY clause in common table expression in sql server

We cannot use ORDER BY clauses in common table expression directly in sql server. For example:  

WITH cteStudent

AS(

    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.

Solution: 

We must have to use either TOP or FOR XML. For example:

WITH cteStudent

AS(

    SELECT TOP(1) * FROM Student ORDER BY 1

)UPDATE cteStudent SET Stu_Name = 'Scott'


Writing dynamic sql queries in sql server
Difference between temporary table and table variable in sql server
Difference between varchar and nvarchar in sql server.
Difference between char and varchar in sql server.
Variables in sql server 2008 R2