If you’re getting an error that reads “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” in SQL Server, it appears that you’re using the ORDER BY clause in a place that it must also be accompanied by a TOP, OFFSET or FOR XML clause, but you’re not including one of those clauses.

To fix this issue, be sure to include one of those clauses in your query if you need to use the ORDER BY clause.

Example of Error

Here’s an example of code that produces the error:

WITH CTE_Example AS (
    SELECT OrderID, CustomerName, OrderDate
    FROM Orders
    ORDER BY OrderDate DESC
)
SELECT *
FROM CTE_Example;

Output:

Msg 1033, Level 15, State 1, Line 5
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.

In this case I’m using the ORDER BY clause inside a common table expression (CTE), but I’m not using the TOP clause. The Microsoft states that the ORDER BY clause cannot be used in CTEs unless it’s accompanied by the TOP clause. That said, the error message states that we can alternatively use OFFSET, or FOR XML.

Same thing when trying to create a view:

CREATE VIEW vGetOrdersByDate AS 
    SELECT OrderID, CustomerName, OrderDate
    FROM Orders
    ORDER BY OrderDate DESC;

Output:

Msg 1033, Level 15, State 1, Procedure vGetOrdersByDate, Line 4
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.

And as the error message states, it would be the same for subqueries, inline functions and derived tables.

Solution

To fix this issue, we must provide include TOP, OFFSET or FOR XML if we want to sort the results of the query. I’ve found that we can also use FOR JSON.

Here’s an example of fixing the CTE:

WITH CTE_Example AS (
    SELECT TOP 1000 OrderID, CustomerName, OrderDate
    FROM Orders
    ORDER BY OrderDate DESC
)
SELECT *
FROM CTE_Example;

Here, I added TOP 1000 to the CTE query and it resolved the issue.

Another way to deal with the issue is to remove the ORDER BY clause altogether (and the TOP clause), and to sort the outer query:

WITH CTE_Example AS (
    SELECT OrderID, CustomerName, OrderDate
    FROM Orders
)
SELECT *
FROM CTE_Example
ORDER BY OrderDate DESC;

Bear in mind that this might not always return the same results as using TOP within the CTE. In particular:

• Order of Rows in the CTE: Without TOP or ORDER BY in the CTE, there is no guarantee that the rows in the CTE are processed in any particular order. SQL Server will optimize the CTE execution, which could lead to rows being processed in any sequence. Moving the ORDER BY to the outer query will ensure the final result is sorted, but the data being processed within the CTE could vary in different executions if you rely on some internal ordering.
• Impact of TOP: The use of TOP inside the CTE ensures that only the top N rows are retrieved and ordered according to the ORDER BY in the CTE itself. This means that the ordering affects which rows are selected before they’re passed to the outer query. If you move the ORDER BY outside and omit TOP, all rows are selected, and only the outer query handles the sorting. The key distinction is that the outer ORDER BY won’t affect which rows are selected or their internal processing order.
• Deterministic vs. Non-Deterministic Results: If there are ties in the OrderDate column (i.e., multiple rows with the same date), the order of these rows could vary between executions when the ORDER BY is only in the outer query. Since there’s no explicit sorting in the CTE, SQL Server might return these tied rows in different orders during different executions.

As for the view, let’s use the OFFSET clause:

CREATE VIEW vGetOrdersByDate AS 
    SELECT OrderID, CustomerName, OrderDate
    FROM Orders
    ORDER BY OrderDate DESC
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY;

In this case I combined it with FETCH to get the first ten rows. This will resolve the issue we were getting when trying to create the view without the OFFSET clause.

Note that the Microsoft documentation states:

The ORDER BY clause is used only to determine the rows that are returned by the TOP or OFFSET clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.

So the question would be why you’re using the ORDER BY clause, and whether merely adding the OFFSET clause will provide the desired result anyway. It may make sense to remove the ORDER BY clause and sort the outer query.

Depending on the exact scenario that’s causing your error, you will need to consider which option is most appropriate (i.e. TOP, OFFSET, FOR XML, or sorting the outer query) in order to fix the error.