title: SQL Server ROW_NUMBER behavior
body: <p>I have the following query:</p>
  
  <pre><code>SELECT * FROM
  (
      SELECT 
         a.TeacherID, a.UniversityID, 
         ROW_NUMBER() OVER (ORDER BY a.TeacherID) AS RowNum 
      FROM 
         SelectAll a  
      LEFT JOIN 
         mp_Ratings r ON a.TeacherID = r.TeacherID 
      GROUP BY 
         a.TeacherID, a.UniversityID
  ) AS TeacherInfo 
  WHERE RowNum BETWEEN 10 AND 50
  </code></pre>
  
  <p>and it works fine. Now, If I want to do something like:</p>
  
  <pre><code>SELECT * FROM
  (
      SELECT 
         a.TeacherID, a.UniversityID, 
         ROW_NUMBER() OVER (ORDER BY a.TeacherID) AS RowNum 
      FROM 
         SelectAll a  
      LEFT JOIN 
         mp_Ratings r ON a.TeacherID = r.TeacherID 
      GROUP BY 
         a.TeacherID, a.UniversityID
  ) AS TeacherInfo 
  WHERE RowNum BETWEEN 10 AND 50 AND UniversityID = 2
  </code></pre>
  
  <p>I get nothing because the <code>UniversityID = 2</code> begins around row number <strong>2991</strong>. I would like to be able to reset the <code>Row_Number</code> with every different <code>UniversityID</code>. </p>
  
  <p>I tried the following:</p>
  
  <pre><code>SELECT *, ROW_NUMBER() OVER (ORDER BY TeacherID) AS RowNum  
  FROM
  (
      SELECT 
          a.TeacherID, a.UniversityID 
      FROM 
          SelectAll a  
      LEFT JOIN 
          mp_Ratings r ON a.TeacherID = r.TeacherID 
      GROUP BY 
          a.TeacherID, a.UniversityID
  ) AS TeacherInfo 
  WHERE UniversityID = 2
  </code></pre>
  
  <p>which brings new set of <code>row_numbers</code> for every search, but if I try to use <code>RowNum</code> alias as such:</p>
  
  <pre><code>SELECT *, ROW_NUMBER() OVER (ORDER BY TeacherID) AS RowNum  
  FROM
  (
      SELECT 
          a.TeacherID, a.UniversityID 
      FROM 
          SelectAll a  
      LEFT JOIN 
          mp_Ratings r ON a.TeacherID = r.TeacherID 
      GROUP BY 
          a.TeacherID, a.UniversityID
  ) AS TeacherInfo 
  WHERE UniversityID = 2 
    AND RowNum BETWEEN 10 AND 20
  </code></pre>
  
  <p>I get </p>
  
  <blockquote>
    <p>Msg 207, Level 16, State 1, Line 4 Invalid column name 'RowNum'.</p>
  </blockquote>
  
  <p>What is wrong with my select? Why can't I access the <code>RowNum</code> alias?</p>