T-SQL Part IV: ORDER BY

ORDER BY 返回一个Cursor，并不返回结果集。而试图将Cursor作为输入将产生了错误。

所以，下列的SQL语句将产生错误：

 

SELECT VerID, IsComplete
FROM ( SELECT TOP 10 VerID, IsComplete
FROM dbo.T_AOTInfo
ORDER BY VerID DESC ) AS TABLE1;

 

错误：

Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

 

同样，试图将上述语句包装为一个View，也会导致同样的错误。

 

要解决这个错误，要么，移除ORDER BY，要么使用TOP， OFFSET或者FOR XML。

SELECT VerID, IsComplete
FROM ( SELECT TOP 10 VerID, IsComplete
FROM dbo.T_AOTInfo
ORDER BY VerID DESC ) AS TABLE1;

 

 

是为之记。

Alva Chien
2016.5.19