title: way to ignore intermediate column in an INSERT SELECT statement?
body: <p>For a <code>insert ... select</code> statement, is there a way to ignore a variable that is required for the select statement but should be inserted? </p>
  
  <p>I'm using a <code>rank</code> function to select which data to <code>insert</code>. Unfortunately the <code>rank</code> function can't be called in the where clause.</p>
  
  <pre><code>insert into target_table(v1
                          ,v2
                          ,v3
                          )
  select v1
        ,v2
        ,v3
        ,RANK() over (partition by group_col
                      order by order_col desc
                     ) as my_rank
  from source_table
  where my_rank &gt; 1
  </code></pre>
  
  <p>The result is the following error:</p>
  
  <blockquote>
    <p>Msg 121, Level 15, State 1, Line 1 The select list for the INSERT
    statement contains more items than the insert list. The number of
    SELECT values must match the number of INSERT columns.</p>
  </blockquote>
  
  <p>I know I can do this using a temporary table but would like to keep it in a single statement if possible.</p>