Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
2  
You are paging on ID so that will decide what rows you get from the CTE so I guess you want to order those rows right? Move the order by out of the CTE to the end of the main query. –  Mikael Eriksson Jun 4 '12 at 15:18
add comment

1 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
share|improve this answer
    
Good. You should still remove the top 100 part. It will do you no good. And you could add order by corr to your main query to make sure the rows you fetch are ordered. –  Mikael Eriksson Jun 4 '12 at 17:02
    
@MikaelEriksson thanks Mikael, will do! –  Ozz Jun 4 '12 at 18:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.