Issue with Order By with FOR XML in T-sql (The ORDER BY clause is invalid in views, inline functions, derived tables)

Question

select a.Hall, a.Title, 
  STUFF((SELECT ', ' + '[' + CONVERT(varchar(2),DATEPART(Hour, b.StartFilm))  
  + ':' + CONVERT(varchar(2),DATEPART(Minute, b.StartFilm)) 
  + ' ' + CONVERT(varchar(2),DATEPART(Hour, b.EndTime))  
  + ':' + CONVERT(varchar(2),DATEPART(Minute, b.EndTime)) 
  + ']' 
FROM (select c.Name as Hall, b.Title, 
    Convert(time,a.StartFilmTime) as StartFilm,  
    Convert(time,a.EndFilmTime) as EndTime
  from FilmSchedule a 
    left join Film b on a.FilmId = b.Id 
    left join Room c on a.RoomId = c.Id 
  where a.ApproveStatus = 1 and a.Status = 1 
        and CONVERT(date, a.StartFilmTime) =  '05-06-2015'
) b 
Where a.Hall = b.Hall and a.Title = b.Title 
FOR XML PATH('')),1,1,'') As ShowTime  
from (select c.Name as Hall, b.Title, 
  Convert(time,a.StartFilmTime) as StartFilm,  
  Convert(time,a.EndFilmTime) as EndTime
  from FilmSchedule a 
  left join Film b on a.FilmId = b.Id 
  left join Room c on a.RoomId = c.Id 
  where a.ApproveStatus = 1 and a.Status = 1 
     and CONVERT(date, a.StartFilmTime) =  '05-06-2015'
  Order by a.StartFilmTime
) a 
group by a.Hall, a.Title

I get the error:

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

Help please! (I have used FOR XML?)

score 2 · Accepted Answer · edited May 23 '17 at 11:43

Although your query does use FOR XML (for the GROUP_CONCAT workaround), you are applying the order by outside of the derived table that uses FOR XML, hence the error.

Given that you aren't including start date directly in the final select (although you are composing it as part of the STUFF ShowTime column), you also can't ORDER BY StartFilm in the final GROUP BY either, as the column would otherwise need to be included in the GROUP BY or as an aggregated column.

What you can do is move the ORDER BY into the STUFF and then order by the derived column ShowTime (since your query only runs for one given day, and StartFilmTime is the first part of the STUFFED composed column).

At the same time, I would DRY up the repetition on the derived table with a CTE:

WITH cteFiltered AS
    (select c.Name as Hall, b.Title, 
        Convert(time,a.StartFilmTime) as StartFilm,  
        Convert(time,a.EndFilmTime) as EndTime
    from FilmSchedule a 
        left join Film b on a.FilmId = b.Id 
        left join Room c on a.RoomId = c.Id 
    where a.ApproveStatus = 1 and a.Status = 1 
          and CONVERT(date, a.StartFilmTime) =  '05-06-2015'
    )
select 
    a.Hall, 
    a.Title, 
    STUFF((SELECT ', ' + '[' + CONVERT(varchar(2),DATEPART(Hour, b.StartFilm))  
    + ':' + CONVERT(varchar(2),DATEPART(Minute, b.StartFilm)) 
    + ' ' + CONVERT(varchar(2),DATEPART(Hour, b.EndTime))  
    + ':' + CONVERT(varchar(2),DATEPART(Minute, b.EndTime)) 
    + ']' 
        FROM
            cteFiltered b
        Where 
            a.Hall = b.Hall and a.Title = b.Title 
        order by b.StartFilm -- ***
        FOR XML PATH('')),1,1,'') As ShowTime  
from 
    cteFiltered a 
group by a.Hall, a.Title
order by ShowTime; -- *** Hour is first (assuming 24H format) and only one day

One other thing to note is that the performance of your query will hurt for large tables because `CONVERT(date, a.StartFilmTime) = '05-06-2015'` isn't SARGable for an index on `StartFilmTime`. Suggest you change this to `a.StartFilmTime >= '05-06-2015' and a.StartFilmTime < '06-06-2015'` or similar. — StuartLC, May 06 '15 at 05:49

Issue with Order By with FOR XML in T-sql (The ORDER BY clause is invalid in views, inline functions, derived tables)

1 Answers1