The ORDER BY clause is invalid in views...

ajks 20 Reputation points
2023-10-04T07:42:54.26+00:00

An error occurred while running the tsql statement.

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.

How can I fix this error?

Could someone please help me out?

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Anonymous
    2023-10-04T07:49:59.32+00:00

    Hi @ajks

    You can 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:

    SELECT ... FROM ... ORDER BY column OFFSET 0 ROWS;
    

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".


2 additional answers

Sort by: Most helpful
  1. Olaf Helper 47,441 Reputation points
    2023-10-04T07:53:23.14+00:00

    Normally the caller of a view defines the ORDER BY he wants to get the data.

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP is specified

    As the message says, define a TOP clause

    0 comments No comments

  2. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2023-10-04T21:29:42.08+00:00

    You remove the ORDER BY clause. A view is an unordered object, so it makes no point to have an ORDER BY clause in views.

    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.