title: How to use ROW_NUMBER in the following procedure?
body: <p>I have the following stored procedure which returns <code>A</code>, <code>B</code>, and the count in descending order.  I am trying to use <code>ROW_NUMBER</code>, so I can page the records, but I want the first row number <code>1</code> to be the record with the highest count, so basically, if I return a table with 3 records and the count is <code>30</code>, <code>20</code>, <code>10</code>, then row number <code>1</code> should correspond with count <code>30</code>, row number <code>2</code> should correspond with count <code>20</code>, and row number <code>3</code> should correspond with count <code>10</code>.  <code>dbo.f_GetCount</code> is a function that returns a count.</p>
  
  <pre><code>create procedure dbo.Test
  as
  @A nvarchar(300) = NULL,
  @B nvarchar(10) = NULL
  as
  
  select @A = nullif(@A,'')
        ,@B = nullif(@B,'');
  
  select h.A
        ,hrl.B
        ,dbo.f_GetCount(hrl.A,h.B) as cnt
  from dbo.hrl
      inner join dbo.h
          on h.C = hrl.C
  where(@A is null
        or h.A like '%'+@A+'%'
       )
       and (@B is null
            or hrl.B = @B
           )
  group by hrl.B
          ,h.A
  order by cnt desc;
  </code></pre>