title: Using Row_Number() Over (Order By Column) When Column is in Different DB Returns Error
body: <p>I am submitting the following query in Sql Server (2008)</p>
  
  <pre><code>WITH query AS (SELECT TOP 100 PERCENT
      ROW_NUMBER() OVER
      (ORDER BY [tbl2].[col2] ASC) AS TableRowNumber ,
      [tbl1].[col1] ,
      [tbl2].[col2]
  FROM [db1].[dbo].[tbl1] AS [tbl1]
  JOIN [db2].[dbo].[tbl2] AS [tbl2]
      ON [tbl1].[id] = [tbl2].[id])
  
  SELECT
      *
  FROM query
  WHERE TableRowNumber BETWEEN 1 AND 15
  ORDER BY TableRowNumber ASC
  </code></pre>
  
  <p>When this query is run, it returns the following error message:</p>
  
  <pre><code>Msg 207, Level 16, State 1, Line 3
  Invalid column name 'col2'. 
  </code></pre>
  
  <p>The sql join itself runs fine (when run as a normal query. The issue seems to be with using the <code>Row_Number() over (Order By COLUMN)</code> when <code>COLUMN</code> is in a different database from the <code>FROM</code> table of the query.</p>
  
  <p>If I would change line 3 to read <code>(Order By [tbl1].[col1] ASC)</code> then it runs without any issues. The error only happens when the sort column is in a different DB.</p>
  
  <p>Does anyone know why this is happening? Any suggestions on how to fix this?</p>