I am facing a strange error in SQL Server and I want some explanation of it.
When I write ORDER BY
in a subquery, for instance
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.
But when I use TOP
in subquery it works normally
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
SELECT a FROM (SELECT * FROM A ORDER BY a) T
In that case. what is the reason of error?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With