The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML

Question

I have a query and i want to order by CreatationDateTime form requestFolders But I get this error.

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.

my query is :

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'

-- the error is here
order by IRF.CreationDateTime

) 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

The error is on Msg 1033, Level 15, State 1, Line 24

Please help me.

Answer 1

In almost all cases, you can simply drop the CTE's ORDER BY clause. Even if it were permitted syntactically (it is in other RDBMS), it has no effect on your query's result the way you wrote it.

Now, if for some reason, you absolutely have to keep it there, you can add a TOP clause, e.g. one without any effect such as TOP 100 PERCENT, i.e.:

with requests as    (
  select top 100 percent
    IRF.Id as Id,
    ...

A full, simplified example:

-- Doesn't work
with t (a) as (
  select a
  from (values (1),(3),(2)) t (a)
  order by a
)
select a from t order by a

-- Works
with t (a) as (
  select top 100 percent a
  from (values (1),(3),(2)) t (a)
  order by a
)
select a from t order by a