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 ‘,’.

Answer

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;