在CTE中使用ORDER BY触发Msg 1033错误，寻求解决方案

这个错误的原因很明确：SQL Server不允许在CTE、视图、派生表这类对象中单独使用ORDER BY子句，除非同时搭配TOP、OFFSET或者FOR XML——因为这些对象本质是临时结果集，单独排序在这里既不生效也不符合语法规则，排序操作应该放到最终输出的查询语句里。

修正后的完整代码 #

with requests as ( 
 select IRF.Id as Id, 
 P.Id as ProcessId, 
 N'Investment' as [ServiceType], 
 IRF.FolderNumber as FolderNumber, 
 P.[Name] as [TypeTitle], 
 S.CustomerDisplayInfo_CustomerTitle as [CurrentStatus], 
 S.CustomerDisplayInfo_CustomerOrder as [CurrentStatusOrder], 
 RH.OccuredDateTime as [CurrentStatusDate], 
 IRF.CreationDateTime as [RequestDate], 
 IRF.RequestedAmount as [Amount], 
 (case when A.Id is not Null and s.sidetype='CustomerSide' then 1 else 0 end) as [HasAction], 
 rank() over ( partition by IRF.Id order by rh.OccuredDateTime desc) as rnk 
 from [Investment].[dbo].[InvestmentRequestFolders] as IRF 
 inner join [Investment].[dbo].[RequestHistories] as RH on IRF.Id = RH.ObjectId 
 inner join [Investment].[dbo].[Processes] as P on P.Id = RH.ProcessId 
 inner join [Investment].[dbo].[Step] as S on S.Id = RH.ToStep 
 left join [Investment].[dbo].[Actions] as A on A.StepId = RH.ToStep 
 where IRF.Applicant_ApplicantId = '89669CD7-9914-4B3D-AFEA-61E3021EEC30' 
 -- 移除原CTE中触发错误的ORDER BY语句
) 
SELECT t.Id, 
 max(t.ProcessId) as [ProcessId], 
 t.[ServiceType] as [ServiceType], 
 isnull(max(t.TypeTitle), '-') as [TypeTitle], 
 isnull(max(t.FolderNumber), '-') as [RequestNumber], 
 isnull(max(t.CurrentStatus), '-') as [CurrentStatus], 
 isnull(max(t.CurrentStatusOrder), '-') as [CurrentStatusOrder], 
 max(t.CurrentStatusDate)as [CurrentStatusDate], 
 max(t.RequestDate) as [RequestDate], 
 max(t.HasAction) as [HasAction], 
 isnull(max(t.Amount), 0) as [Amount] 
FROM requests as t 
where t.rnk = 1 
GROUP BY t.Id
-- 将排序逻辑移到最终查询结果中
ORDER BY t.RequestDate;

关键修改说明 #

1. 移除CTE中的ORDER BY：原CTE里的order by IRF.CreationDateTime是触发错误的直接原因，CTE作为临时结果集不需要（也不允许）在这里做排序。
2. 将排序移到最终查询：把排序逻辑放到主查询的GROUP BY之后，通过ORDER BY t.RequestDate实现——RequestDate就是你原本要排序的IRF.CreationDateTime，这样既符合语法规则，又能得到你想要的排序结果。

额外补充：CTE内部的排序不会被保留到后续查询，就算用TOP 100 PERCENT这类旧技巧（现在已不推荐），也无法保证后续查询的排序稳定性，所以正确做法始终是在最终输出的SELECT语句中指定ORDER BY。

内容的提问来源于stack exchange，提问作者ali lotfi