Writing Valid ORDER BY Queries for Views and CTEs in SQL Server

If you’ve worked with SQL Server long enough, you’ve probably hit the dreaded error when trying to use ORDER BY inside a view or CTE. It usually shows up as something like:

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.

This rule can come as a bit of a surprise because it feels natural to want a query “pre-sorted” when wrapping it in a view or CTE. The problem is that SQL Server doesn’t allow the ORDER BY clause in this context unless it’s in conjunction with the clauses mentioned in the error message. Without such clauses, you need to explicitly request it at the outermost SELECT.

Let’s walk through an example of how to handle this.

Example Setup

The following script creates a simple Sales table with some data that we’ll use to explore the issue:

-- Drop if exists
DROP TABLE IF EXISTS dbo.Sales;
GO

CREATE TABLE dbo.Sales
(
    SaleID INT IDENTITY PRIMARY KEY,
    CustomerName NVARCHAR(50),
    SaleAmount DECIMAL(10,2),
    SaleDate DATE
);
GO

INSERT INTO dbo.Sales (CustomerName, SaleAmount, SaleDate)
VALUES
('Nadia Ortega',   420.00, '2025-01-15'),
('Samir Qureshi',  150.00, '2025-01-16'),
('Ivy Chen',        85.50, '2025-01-17'),
('Jonas Richter',  990.00, '2025-01-18'),
('Marta Kowalski', 310.00, '2025-01-19');
GO

Now we’re ready to try building a view.

The Wrong Way: `ORDER BY` in the View

You might be tempted to do something like this:

CREATE VIEW dbo.TopSalesWrong
AS
    SELECT CustomerName, SaleAmount, SaleDate
    FROM dbo.Sales
    ORDER BY SaleAmount DESC;

Output:

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

SQL Server rejects this with the aforementioned error. The engine doesn’t want you to assume the rows are ordered unless you explicitly force that in a context that supports it. A view is supposed to be a logical representation of a set of rows, not a stored sorting request.

Even if this did compile, SQL Server could still ignore the order when querying the view. So relying on it would be unsafe anyway.

The Correct Way: Push `ORDER BY` to the Outer Query

The fix is to keep the view focused on the rowset definition and let the consumer decide on ordering.

For example, we can create the view like this:

CREATE VIEW dbo.TopSales
AS
    SELECT CustomerName, SaleAmount, SaleDate
    FROM dbo.Sales;

And now we can use ORDER BY when querying the view:

SELECT CustomerName, SaleAmount, SaleDate
FROM dbo.TopSales
ORDER BY SaleAmount DESC;

Result:

CustomerName         SaleAmount   SaleDate        
-------------------- ------------ ----------------
Jonas Richter              990.00       2025-01-18
Nadia Ortega               420.00       2025-01-15
Marta Kowalski             310.00       2025-01-19
Samir Qureshi              150.00       2025-01-16
Ivy Chen                    85.50       2025-01-17

Now the sorting happens safely at query time, not when defining the view.

Special Cases: When `ORDER BY` Is Allowed

As we’ve seen, we normally can’t use ORDER BY in a view or CTE, but there are three exceptions:

TOP – to define which rows qualify for inclusion.
OFFSET/FETCH – to implement paging logic.
FOR XML – to control ordering in the generated XML output.

These are cases where SQL Server requires it because the ordering has a direct impact on the result.

Here are examples for each, using the Sales table we set up earlier:

-- TOP with ORDER BY (highest sale only)
CREATE VIEW dbo.HighestSale
AS
    SELECT TOP 1 CustomerName, SaleAmount, SaleDate
    FROM dbo.Sales
    ORDER BY SaleAmount DESC;
GO

-- OFFSET/FETCH with ORDER BY (skip 2, take next 2)
CREATE VIEW dbo.SecondPageOfSales
AS
    SELECT CustomerName, SaleAmount, SaleDate
    FROM dbo.Sales
    ORDER BY SaleAmount DESC
    OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;
GO

-- FOR XML with ORDER BY (alias required for the XML output)
CREATE VIEW dbo.SalesAsXml
AS
    SELECT
        (
            SELECT CustomerName, SaleAmount, SaleDate
            FROM dbo.Sales
            ORDER BY SaleDate
            FOR XML AUTO
        ) AS SalesXml;
GO

Each of these are valid, because the ORDER BY clause is essential to how SQL Server interprets the query.

Take the first view for example. Without the ORDER BY clause, SQL Server wouldn’t know what TOP 1 actually means. TOP 1 by itself is somewhat meaningless if the results are returned in an arbitrary order. We could get a different result each time we ran the query, which would result in a different “top” row being returned each time. Which begs the question: The top row based on what?. Therefore, sorting the query is an integral part of the functionality – we’re returning the top row, based on the sort order.

Of course, we can specify any number of rows with the TOP clause (we’re not limited to just the top 1). But the same principle applies. It’s the top N rows based on the sort order.

CTEs and `ORDER BY`

CTEs follow the same rule. Writing this will fail:

WITH OrderedSales AS
(
    SELECT CustomerName, SaleAmount
    FROM dbo.Sales
    ORDER BY SaleAmount DESC
)
SELECT *
FROM OrderedSales;

Instead, we need to move the ORDER BY outside:

WITH OrderedSales AS
(
    SELECT CustomerName, SaleAmount
    FROM dbo.Sales
)
SELECT *
FROM OrderedSales
ORDER BY SaleAmount DESC;

Or, if you really need the top N rows:

WITH TopTwoSales AS
(
    SELECT TOP 2 CustomerName, SaleAmount
    FROM dbo.Sales
    ORDER BY SaleAmount DESC
)
SELECT *
FROM TopTwoSales;

Main Takeaways

Views and CTEs define a rowset, not its order.
Always push ORDER BY to the outer query unless paired with TOP, OFFSET, or FOR XML.
If you think you need an ordered view, stop and ask whether what you actually want is just a reusable query definition and leave sorting to the consumer.

In short, don’t try to “bake in” ordering where it doesn’t belong. Let SQL Server do its thing, and only order the data when you’re actually consuming it.

Example Setup

The Wrong Way: ORDER BY in the View

The Correct Way: Push ORDER BY to the Outer Query

Special Cases: When ORDER BY Is Allowed

CTEs and ORDER BY

Main Takeaways

The Wrong Way: `ORDER BY` in the View

The Correct Way: Push `ORDER BY` to the Outer Query

Special Cases: When `ORDER BY` Is Allowed

CTEs and `ORDER BY`