子查询中的SQL Server ORDER BY子句

I am facing a strange error in SQL Server and I want some explanation of it.

我在SQL Server中遇到一个奇怪的错误,我想要一些解释。

When I write ORDER BY in a subquery, for instance

例如,当我在子查询中编写ORDER BY时

SELECT a FROM (SELECT * FROM A ORDER BY a) T

it throws the following 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.

除非还指定了TOP或FOR XML,否则ORDER BY子句在视图,内联函数,派生表,子查询和公用表表达式中无效。

But when I use TOP in subquery it works normally

但是当我在子查询中使用TOP时,它正常工作

 SELECT a 
 FROM
    (SELECT TOP 1000000 * FROM A ORDER BY a) T

So, does it mean that I can select top row count of A, instead of

那么,这是否意味着我可以选择A的最高行数,而不是

SELECT a FROM (SELECT * FROM A ORDER BY a) T

In that case. what is the reason of error?

在这种情况下。错误的原因是什么?

1 个解决方案

#1

There is no much sense to sort the subquery and after that select something from it - it is not guaranteed that top-level select will be ordered, so - there is no sense to order the inner query

排序子查询没有太大意义,之后从中选择一些东西 - 不能保证*选择将被排序,所以 - 没有意义来排序内部查询

But if you order inner query with TOP statement - it also not guaranteed that top level select will be ordered in such a way, but it will contain only top X rows from the inner query - that is already makes sense.

但是如果你用TOP语句命令内部查询 - 它也不能保证*选择将以这种方式排序,但它只包含来自内部查询的前X行 - 这已经是有意义的。