CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Moshin Khan
Moshin Khan

Reputation: 185

TSQL-ORDER BY clause in a CTE expression?

Can we use ORDER BY clause in a CTE expression?

;with y as
(
     select 
         txn_Date_Time, txn_time, card_No, batch_No, terminal_ID
     from 
         C1_Transaction_Information
     where 
         txn_Date_Time = '2017-10-31'
     order by 
         card_No
)
select * from y;

Error message:

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

Msg 102, Level 15, State 1, Line 25
Incorrect syntax near ','.

Upvotes: 5

Views: 31815

Answers (6)

Cassius Clay
Cassius Clay

Reputation: 332

There are couple of ways to actually use a order by inside a cte.

Method-1 (using offset)

WITH cte AS
    (
    SELECT * 
    FROM A
    ORDER BY [mycol] ASC
    OFFSET 0 ROWS
    )
SELECT * FROM cte

Method-2 (using TOP 100 percent)

WITH cte AS
    (
    SELECT TOP 100 PERCENT * 
    FROM A
    ORDER BY [mycol] ASC
    )
SELECT * FROM cte

Upvotes: 2

Goldfish
Goldfish

Reputation: 702

I needed to use the ORDER BY CLAUSE INSIDE the Common Table Expression. I was building up a SQL SELECT QUERY STATEMENT combining all the column names. I had to sort by the column ORDINAL_POSITION.

This is a way to force to use of the ORDER BY clause. After using the ORDER BY clause use OFFSET 0 ROWS.

WITH CTE_Example
AS
(
SELECT *
FROM A
ORDER BY [COL_A] ASC
OFFSET 0 ROWS
)
SELECT *
FROM CTE_Example

Upvotes: 6

Ludovic Aubert
Ludovic Aubert

Reputation: 10526

You can use ORDER BY in a cte if the cte delivers JSON

WITH cte(n) AS (
    SELECT 1
    UNION ALL
    SELECT 2
), cte2(j) AS (
    SELECT n 
    FROM cte
    ORDER BY n
    FOR JSON PATH
)
SELECT * FROM cte2;

enter image description here

The rationale is you can use ORDER BY in final output. Before final output, you can keep the columns necessary for the ordering to be called later.

Upvotes: 0

whytheq
whytheq

Reputation: 35557

A good alternative is to use ROW_NUMBER inside the CTE:

;with y as
(
     select  
         rn = ROW_NUMBER() OVER (ORDER BY card_No),
         txn_Date_Time, 
         txn_time, 
         card_No, 
         batch_No, 
         terminal_ID
     from 
         C1_Transaction_Information
     where 
         txn_Date_Time = '2017-10-31'
)
select txn_Date_Time, 
         txn_time, 
         card_No, 
         batch_No, 
         terminal_ID
from y 
order by rn;

This gives you the option to then select TOP 10 as TOP ...ORDER BY is not allowed within a CTE:

;with y as
(
     select  
         rn = ROW_NUMBER() OVER (ORDER BY card_No),
         txn_Date_Time, 
         txn_time, 
         card_No, 
         batch_No, 
         terminal_ID
     from 
         C1_Transaction_Information
     where 
         txn_Date_Time = '2017-10-31'
)
select txn_Date_Time, 
         txn_time, 
         card_No, 
         batch_No, 
         terminal_ID
from y 
where rn <= 10;

Upvotes: 12

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

FYI https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

The following clauses cannot be used in the CTE_query_definition:

ORDER BY (except when a TOP clause is specified)

INTO

OPTION clause with query hints

FOR BROWSE

Upvotes: 1

SteveB
SteveB

Reputation: 924

You can't use "Order By" in a CTE but you can move the order by to the select statement calling the CTE and have the affect I believe you are looking for

;with y as(
select txn_Date_Time,txn_time,card_No,batch_No,terminal_ID
from C1_Transaction_Information
where txn_Date_Time='2017-10-31'

)

select * from y order by card_No;

Upvotes: 9

Related Questions