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.

Rabia Kurnaz 386 Reputation points
2022-10-25T08:03:57.043+00:00

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.

i get the error can you help me?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,136 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,665 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 29,111 Reputation points
    2022-10-26T06:05:16.173+00:00

    Hi @Rabia Kurnaz
    As experts answered, just remove the ORDER BY clause in views, inline functions, derived tables, subqueries, and common table expressions.
    Or, as the error message said, specify TOP like this: SELECT TOP (100) PERCENT ... FROM ... ORDER BY column or OFFSET like this: ORDER BY column DESC OFFSET 0 ROWS. However, both seems meaningless to me.

    Best regards,
    Li Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    2 people found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Viorel 118.4K Reputation points
    2022-10-25T13:58:18.81+00:00

    Try removing the ORDER BY that generate the error, or show details to reproduce or ilustrate the problem.


  2. Olaf Helper 45,291 Reputation points
    2022-10-26T04:58:32.437+00:00

    i get the error can you help me?

    And now we have to guess the SQL Statement you run? We can not. Please post it or simply do what the message say: Remove the ORDER BY clause.


  3. Nerma 1 Reputation point
    2022-12-07T15:37:21.903+00:00

    If you really need the order by in your view (consider impact on performances!), you can add the 'TOP 100 PERCENT' to your view definition

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.