title: Help with ORDERING ROW_NUMBER OVER the Count in Descending Order?
body: <p>I am using the ROW_NUMBER() function introduced in SQL SERVER 2005 to return a paged set of results.  The Query Works as expected, but I have one issue.  What I would like to do is return the results ordered by the count in descending order.  Here is the query and below I will give a little description:</p>
  
  <pre><code>DECLARE @StartRowIndex INT
  DECLARE @MaximumRows INT
  
  SET @StartRowIndex = 1
  SET @MaximumRows = 10
  
  ;WITH Data AS (SELECT
          ROW_NUMBER() OVER (ORDER BY a.column1) as RowNum,
          a.column1, a.column2, dbo.f_GetDataCount(a.column3) as cnt
          FROM dbo.table1 a
          INNER JOIN dbo.table2 b on b.column4 = a.column4
          INNER JOIN dbo.table3 c on c.column5 = a.column5
          LEFT OUTER JOIN dbo.table4 d on d.column6 = a.column6
          WHERE 1=1  AND a.column7 IN (1)
  )
  
  
  SELECT RowNum,column1,
     column2,cnt
  FROM
  Data
  WHERE RowNum BETWEEN @StartRowIndex AND (@StartRowIndex + @MaximumRows) - 1
  ORDER BY cnt desc
  </code></pre>
  
  <ol>
  <li>I know the highest count is in the 100,000+ range.</li>
  <li>ORDER By cnt desc  does order the results by their count in descending order, but
  only for the 10 records it fetches.  I know this is because RowNum is being ordered by a.Column1 which is not the count.  Ideally, I would like to Order RowNum by the Count, so something like this:</li>
  </ol>
  
  <p>ROW_NUMBER() OVER (ORDER BY Count(*)) as RowNum</p>
  
  <p>The above runs, but it takes forever (17+ minutes).</p>
  
  <p>As a side note, dbo.f_GetDataCount(a.column3) as cnt returns the total number of records based on a.column3, so I tried:</p>
  
  <p>ROW_NUMBER() OVER (ORDER BY dbo.f_GetDataCount(a.column3) as RowNum, but this took forever as well.</p>
  
  <p>If I am actually confusing this more than what it is, I will give a little example.  If I set the StartRowIndex to 1, it will return 10 records and the first record will have 10,000.</p>
  
  <p>If I then set StartRowIndex to 11, it will return the next 10 records and the first record will have 15,000.</p>
  
  <p>What it actually should return is the record with 15,000 first no matter what the StartRowIndex is.</p>
  
  <p>Help is appreciated very much.</p>
  
  <p>Here is the code for the function:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[f_GetDataCount] 
  (
  -- Add the parameters for the function here
  @column3 nvarchar(10)
  )
  RETURNS int
  AS
  BEGIN
  -- Declare the return variable here
  DECLARE @Result int
  
  -- Add the T-SQL statements to compute the return value here
  SELECT @Result = COUNT(a.column3) FROM dbo.table1 a
  where a.column3 = @column3
  
  -- Return the result of the function
  RETURN @Result
  
  END
  </code></pre>