TSQL-ORDER BY clause in a CTE expression?

Question

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 ','.

Solution 1

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;

Solution 2

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;

Solution 3

FYI https://docs.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

Comments

[Gordon Linoff]

Clearly, you cannot. I think the error message pretty much answers your question.

[Damien_The_Unbeliever]

The error message already told you no, and told you what changes you could make that would allow it. If you've got a question, it's unclear what you're asking that isn't covered by the error message.

[Bernesto]

Exactly what I needed! Thx!