The ORDER BY clause is invalid in views...

Question

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?

Answer 1

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".

Answer 2

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

Answer 3

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.