The ORDER BY clause is invalid

Question

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.

Tried using TOP (100) PERCENT. However it seems not working in query like this:

;with cte as
(
select TOP (100) PERCENT * 
from tbl
order by col1
)
select * from cte

The final result is still unordered.

Answer 1

Hi @Lylyy

The final result is still unordered.

Why not move the order by clause to the outside select clause?

The cte(common_table_expression) is just a temporary named result set, it is meaningless to use order by in the definition.

The only effect of TOP 100 percent here is to make SQL server ignore the syntax error. The data of cte result set is still unordered.

Best regards,

Cosmog Hong