title: SQL Server row_number
body: <p>I have a problem to create a custom <code>rownumber</code> with an alias column. Here are some sample data:</p>
  
  <p>Table <code>question</code>:</p>
  
  <pre><code>id   title   
  --   --------    
  1     xx
  2     xxx
  </code></pre>
  
  <p>..</p>
  
  <p>Table <code>customerLikeQuestion</code>:</p>
  
  <pre><code>Id   QuestionId CustomerId
  ---------------------------
  1       20          xx
  2       100         xx 
  xx
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>SELECT  q.Id,
          (SELECT     COUNT(*) 
           FROM          dbo.CustomerLikeQuestion
           WHERE      (QuestionId = q.Id)) AS LikeCount
  FROM dbo.Question AS q
  ORDER BY likecount DESC
  </code></pre>
  
  <p>The previous query shows:</p>
  
  <pre><code> id    LikeCount
  2136    6
  2138    5
  2150    5
  </code></pre>
  
  <p>Now I'd like to put a <code>rownumber</code> to count an incremental order of a row. I tried the following query:</p>
  
  <pre><code>SELECT     TOP (100) PERCENT Id,
             (SELECT     COUNT(*) AS Expr1
              FROM          dbo.CustomerLikeQuestion
              WHERE      (QuestionId = q.Id)) AS LikeCount, 
              row_number() over (order by likecount) as RowNum
  FROM         dbo.Question AS q
  ORDER BY likecount DESC
  </code></pre>
  
  <p>But it gives me the following error:</p>
  
  <blockquote>
    <p><em>invalid column Likecount.</em></p>
  </blockquote>
  
  <p>I do know that <code>Over()</code> cannot work with alias, but how can I get around this problem, either with CTE or subquery, I have not yet come up with any idea. please help.</p>
  
  <p>The right result should be like this: </p>
  
  <pre><code>id  likecount, rownum
  ----------------------    
  xx     6        1
  xx     5        2 
  xx     4        3
  ..     0        xx
  </code></pre>