title: SQL Server 2005 problem with ROW_NUMBER() OVER (ORDER BY ...)
body: <p>This question is in relation to a previous question I've asked ... see "SQL Server 2005 How to order a recordset when using the LIKE operator".</p>
  
  <p>The following works ...</p>
  
  <pre><code>with xx as
  (
  select  case
      when mycol = 'finance' then 1
      when mycol like 'finance%' then 2
      when mycol like '%finance%' then 3
      end as rnk, 
      *
  from    MyTable
  where   mycol like '%finance%'
  )
  
  select * from xx 
  order by xx.rnk, xx.mycol;
  </code></pre>
  
  <p>But what I really want to do is this, as I'm using the server to page through records ...</p>
  
  <pre><code>WITH xx AS
  (
  select  case
      when t.term = 'finance' then 1
      when t.term like 'finance%' then 2
      when t.term like '%finance%' then 3
      end as rnk, 
      *, 
      row_number() over (order by rnk, t.term) as rownumber
  from    tblTerms t
  where   t.term like '%finance%'
  )
  
  select * from xx 
  where rownumber between 11 and 20 -- page #2
  order by xx.rnk, xx.mycol;
  </code></pre>
  
  <p>I'm getting an error " Invalid column name 'rnk' ".</p>
  
  <p>Does anyone have any ideas on how to solve this problem?</p>
  
  <p>Many thanks,</p>
  
  <p>Mark</p>