SQL: Unless you also specify TOP or for XML, the ORDER by clause is in view, inline function, derived table, subquery

Execute SQL statement:

SELECT * FROM (

SELECT * from tab where id>20 order by UserID Desc

As a order by date desc

Logically looking pretty right but error:

The ORDER by clause is not valid in views, inline functions, derived tables, subqueries, and common table expressions unless you specify also TOP or for XML.

As long as we add in the nested subqueries view: Top percent

SELECT * FROM (

Select Top Percent * from Tab where id>20 order by UserID Desc

As a order by date desc

By default, if you try to use ORDER by in a subquery, function, view,

VIEW dbo. Vsortedordersas SELECT OrderID, CustomerID fromdbo. Orders byOrderidgo   

Then you may encounter the following error

For XML, otherwise, the byclause is invalid in views, inline functions, derived tables, subqueries, and common table expressions. 

The reason is that a select for a table does not actually return a table, but rather a cursor.

What if it must be used? The answer is to match top PERCENT

SELECT     PERCENT OrderID, CustomerIDfrom         dbo. Ordersby OrderID, CustomerID DESC  

SQL: Unless you also specify TOP or for XML, the ORDER by clause is in view, inline function, derived table, subquery