A common table expression (CTE) in SQL Server is used to create a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can simplify complex queries and improve readability. However, there are specific rules for using the ORDER BY clause inside a CTE.

Basic Syntax of a CTE

Just so we’re on the same page, the syntax of a CTE goes something like this:

WITH CTE_Name (Column1, Column2, ...)
AS
(
    SELECT Column1, Column2, ...
    FROM SomeTable
    -- Optional WHERE, JOIN, etc.
)
SELECT * FROM CTE_Name;

So, we start with the WITH keyword, then the CTE’s name and definition, and then after all that, we can use a SELECT statement to query the results from the CTE.

We can quite easily use an ORDER BY clause in the main/outer query (in this example that would be the last SELECT statement), but if we want to include one in the CTE itself, we need to adhere to some rules.

The Rules for Using ORDER BY Inside a CTE

The Microsoft documentation states that the ORDER BY clause cannot be used inside a CTE unless it’s part of a TOP clause (to limit the result set). I’ve found that we can also use the OFFSET clause, FOR XML, or FOR JSON instead of TOP if required.

If we include the ORDER BY clause without any of these clauses, we’ll get an error.

Here’s an example of the error:

WITH TopEmployees AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
)
SELECT * FROM TopEmployees;

Output:

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

As the error message states, we can only use the ORDER BY clause inside the CTE if there’s also a TOP, OFFSET, or FOR XML clause. Maybe the error message should also mention FOR JSON, as I’ve found that that clause can also be used without causing this error.

Perhaps this limitation is because the CTE’s purpose is to build a result set for the subsequent query to manipulate. So we would normally sort the results in the subsequent query rather than inside the CTE itself.

In any case, let’s walk through the different ways we can use the ORDER BY clause inside the actual CTE query.

Example 1: Using ORDER BY with TOP Inside a CTE

Here’s an example of valid usage of ORDER BY with the TOP clause within a CTE:

WITH TopEmployees AS
(
    SELECT TOP 5 EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
)
SELECT * FROM TopEmployees;

In this example, the CTE returns the top 5 employees with the highest salary, using ORDER BY in conjunction with TOP. The outer/subsequent query can then use those 5 results as it requires. In this case it’s simply selecting everything, but in practice we would normally do more than that. This is just an overly simplistic example to demonstrate the point.

Example 2: Using ORDER BY with OFFSET Inside a CTE

Here’s an example of valid usage of ORDER BY with the OFFSET clause within the CTE:

WITH PaginatedEmployees AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
)
SELECT * FROM PaginatedEmployees;

Here, I’m using OFFSET and FETCH to achieve return 5 rows, ordered by salary. These clauses are often used to perform pagination, as it allows us to continue the sequence for subsequent rows. For example, the next page could be OFFSET 5 FETCH NEXT 5 ROWS ONLY, and so on.

Example 3: Using ORDER BY with FOR XML Inside a CTE

If you need to output the CTE query’s results in XML format, you can use the FOR XML clause. This will enable you to sort the results without having to use TOP or OFFSET.

Here’s an example of valid usage of ORDER BY with the FOR XML clause within the CTE:

WITH EmployeesXML (EmployeeData) AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    FOR XML PATH('Employee')
)
SELECT * FROM EmployeesXML;

This returns the results in XML format.

Example 4: Using ORDER BY with FOR JSON Inside a CTE

If you need to output the CTE query’s results in JSON format, you can use the FOR JSON clause.

Example:

WITH EmployeesJSON (EmployeeData) AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    FOR JSON PATH, ROOT('Employees')
)
SELECT * FROM EmployeesJSON;

This returns the results in JSON format.

Sorting in the Outer Query

Another option is to use ORDER BY in the outer query. This removes the ORDER BY clause from the CTE and places it in the subsequent query. When we do this, there aren’t any restrictions like there were when we had the ORDER BY clause inside the CTE.

Here’s an example of putting it in the outer query:

WITH EmployeesCTE AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
)
SELECT * 
FROM EmployeesCTE
ORDER BY Salary DESC;

In this case, the sorting is applied after the CTE’s result set is returned.

Implications of Using ORDER BY in a CTE vs Outer Query

• Performance: Sorting in the outer query may offer better performance. Check your query plan to compare each method.
• Flexibility: Sorting in the outer query allows you to reuse the CTE’s result set in different ways, with different ORDER BY clauses, without modifying the CTE itself.
• Readability: Sorting in the outer query can improve readability by separating data extraction (in the CTE) from presentation (in the outer query).
• Limitations: ORDER BY inside a CTE is only allowed when combined with the clauses mentioned above, which limits its use cases.