The ORDER BY clause is invalid

Lylyy 380 Reputation points
2023-10-31T02:33:13.67+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,069 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 24,361 Reputation points
    2023-10-31T02:59:31.95+00:00

    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


0 additional answers

Sort by: Most helpful