title: Insert values into temp Table with Partition By
body: <p>If have a temporary table declared as following:</p>
  
  <pre><code>CREATE TABLE #tempKleerkast(
  id INT IDENTITY NOT NULL PRIMARY KEY,
  IDKleerkastPersoon int,
  ID_Persoon int,
  rk int
  )
  </code></pre>
  
  <p>Now i wish to fill up the table with the values from an actual table where i want to get the <strong>latest</strong> (Not Max) record for each person. Therefor i'm using the following query:</p>
  
  <pre><code>      SELECT 
        DISTINCT IDKleerkastPersoon AS Expr1, kk.ID_Persoon,
        ROW_NUMBER() OVER (PARTITION BY kk.ID_Persoon
                              ORDER BY kk.IDKleerkastPersoon DESC) AS rk
        FROM KleerkastPerPersoon kk
        WHERE  kk.ID_Persoon IS NOT NULL AND rk = 1
  </code></pre>
  
  <p>The problem is where is when i wish to use <strong>insert into</strong> as following:</p>
  
  <pre><code>      INSERT INTO #tempKleerkast(IDKleerkastPersoon,ID_Persoon,rk)
        SELECT 
        DISTINCT IDKleerkastPersoon AS Expr1, kk.ID_Persoon,
        ROW_NUMBER() OVER (PARTITION BY kk.ID_Persoon
                              ORDER BY kk.IDKleerkastPersoon DESC) AS rk
        FROM KleerkastPerPersoon kk
        WHERE  kk.ID_Persoon IS NOT NULL AND rk = 1
  </code></pre>
  
  <p>I get the following error: <strong>Invalid column name 'rk'.</strong>
  Any idea why he doesn't recognise the column rk?</p>
  
  <p><strong>Note:</strong> The column rk is needed further on in the stored procedure. So i can't exclude it.</p>