title: Dynamic order by in Paging SQL Query
body: <p>I would like to have paging &amp; dynamic <code>orderby</code> clause in a stored procedure (THAT TOO ON MORE THAN ONE COLUMN). I did try following but gives me an error</p>
  
  <p>It seems I cannot use Row_number() over rank()</p>
  
  <blockquote>
    <p>Windowed functions cannot be used in the context of another windowed function or aggregate.</p>
  </blockquote>
  
  <p>Is there any alternative to achieve this one, apart from linq to sql</p>
  
  <pre><code>SELECT  [t8].[AssetId], 
              [t8].[WorkOrderId], 
              [t8].[IssueDescription] AS [WorkOrderDescription], 
              [t8].[value] AS [Type], 
              [t8].[WorkOrderStatusTypeName] AS [Status],
              [t8].[value2] AS [StartDate], 
              [t8].[CompletedDate] AS [CompleteDate], 
              [t8].[value22] AS [CompletedBy], 
              ISNULL([t8].[value3],0) AS [Hours]
      FROM    (
                  SELECT  ROW_NUMBER() 
                      OVER (ORDER BY  CASE WHEN @sortColumnName = 'default' THEN (RANK() over( order by [t7].[WorkOrderStatusTypeId] ASC, [t7].[WorkOrderId])) END ,
                                      CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'asc' THEN [t7].[WorkOrderId] END ASC,
                                      CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'desc' THEN [t7].[WorkOrderId] END DESC
                      ) AS [ROW_NUMBER],  
                      [t7].[AssetId], 
                      [t7].[WorkOrderId], 
                      [t7].[IssueDescription], 
                      [t7].[value], 
                      [t7].[WorkOrderStatusTypeName], 
                      [t7].[value2], 
                      [t7].[CompletedDate],
                      [t7].[value22], 
                      [t7].[value3]
              from    --Different tables      
              ) as t8     
      WHERE       [t8].[ROW_NUMBER] BETWEEN ((@pageIndex-1) * @pageSize)+ 1 AND @pageIndex * @pageSize
      ORDER BY    [t8].[ROW_NUMBER]
  </code></pre>