title: Select all second highest values only from temp table
body: <p>Using T-SQL (SQL Server 2008 R2), I'm trying to list only the rows with the second highest value in a particular column from a temp table and then place the results into a new temp table.  The PK is the <code>ID</code>, which can have increasing version numbers and then unique codes. </p>
  
  <p>Example:</p>
  
  <pre><code> ID | Name| Version | Code
  ------------------------
   1  | A   | 1       | 10
   1  | A   | 2       | 20
   1  | A   | 3       | NULL
   2  | B   | 1       | 40
   2  | B   | 2       | 50
   2  | C   | 1       | 60
  </code></pre>
  
  <p>The desired outcome of the query is </p>
  
  <pre><code>ID |  Version | Code
  ------------------------
  1  |  2       | 20
  2  |  1       | 40
  </code></pre>
  
  <p>To achieve this I need the below query to be adapted to pull the second highest value as long as the result gives a version number greater than 1.  These results come from a temp table and will then be placed into a final results temp table.  <strong>EDIT: Please note this will be applied over 33000 rows of data so I would prefer something neater than INSERT VALUES. Thanks.</strong></p>
  
  <p>Current query:</p>
  
  <pre><code>SELECT
      ID
      ,Version
      ,Code
  INTO 
      #table2
  FROM
      #table1
  
  SELECT * 
  FROM #table2
  WHERE Version &gt; 1
  ORDER BY ID asc
  
  DROP TABLE #table1
  DROP TABLE #table2
  </code></pre>
  
  <p>I have tried running the where clause <code>WHERE Version &lt; (SELECT MAX(VERSION) FROM #TABLE 2)</code> but this has no effect, presumably due to the unique code values and in any case wouldn't work where I have more than 3 Versions.  </p>
  
  <p>Ideas would be gratefully received.</p>
  
  <p>Thanks in advance.</p>