Ordering with custom paging when using pivot to convert a query

Question

This is a follow up to my question on changing a flat result set into a different column set using Pivot.

Scripts for test DB can be found in that link

Ordering works just fine like this:

SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr 
        FROM 
        ( 
            select p.ID, firstname,[Date], e.Title as stringvalue  
    from     
            dbo.DayPart dp 
         LEFT outer JOIN 
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID   
        left outer JOIN 
            dbo.Person p ON idp.PersonID = p.ID  
              LEFT outer JOIN 
        dbo.Event e ON e.ID = idp.DayPartID 


        ) as d 
        pivot 
        ( 
            min([stringvalue])     
            for [Date] in  
          ([01/01/12],[02/01/12],[03/01/12],[04/01/12],[05/01/12],[06/01/12],[07/01/12])   
        )  
        as p    
        ORDER BY firstname desc

I have added custom paging to the query and that works fine.

I then wanted to add ordering to the query and I'm coming against the well known issue "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."

I am aware there are issues with using TOP, however I have gave it a bash anyway. When I try to do the order within my CTE, the order does not affect the query at all.

EDIT: having issue uploading the declare statements in bold, please remove the dot in each to get the script to run properly. Every time I try to edit those, my connection crashes.... totally bizarre

DECLARE @StartDate DATE = '20120101';       
**D.ECLARE** @SortBy varchar(10) = 'FirstName'
        **D.ECLARE** @sortDir varchar(10) = 'desc'
    DECLARE @size int = 2
    DECLARE @page int = 1
SELECT TOP (7)  
    @sql += N',[' + CONVERT(CHAR(10), DATEADD(DAY, ROW_NUMBER() OVER  
      (ORDER BY [object_id])-1, @StartDate), 120) + ']' 
    FROM sys.all_objects ORDER BY [object_id]; 

    SELECT @sql = N'
    WITH CTE AS 
    ( 
        SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr 
        FROM 
        ( 
            select p.ID, firstname,[Date], e.Title as stringvalue  
    from     
            dbo.DayPart dp 
         LEFT outer JOIN 
            dbo.PersonDayPart idp ON dp.ID = idp.DayPartID   
        left outer JOIN 
            dbo.Person p ON idp.PersonID = p.ID  
              LEFT outer JOIN 
        dbo.Event e ON e.ID = idp.DayPartID 


        ) as d 
        pivot 
        ( 
            min([stringvalue])     
            for [Date] in  
          (' + STUFF(@sql, 1, 1, '') + ')  
        )  
        as p    
        ORDER BY ' + @sortBy + ' ' + @sortDir + '
    )
    SELECT * 
    FROM CTE 
    WHERE Corr BETWEEN ('+ CONVERT(varchar(2), @size) + ' * (' + CONVERT(varchar(3), @page) + ' - 1))+1 AND ('+ CONVERT(varchar(2), @size) + '*' + CONVERT(varchar(3), @page) + ')
    ' 

    print @sql
    EXEC sp_executesql @sql

Answer 1

Mikael's question helped me immensely.

I was not paying attention to the OVER clause which has an OrderBy.

Altering that got the ordering working correctly with the custom paging.

So I replace the line:

SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr

with the line:

SELECT *, ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ' ' + @sortDir + ') Corr