title: Selecting most recent and specific version in each group of records, for multiple groups
body: <p>The problem:<br>
  I have a table that records data rows in <code>foo</code>. Each time the row is updated, a new row is inserted along with a revision number. The table looks like:</p>
  
  <pre><code>id  rev field
  1   1   test1
  2   1   fsdfs
  3   1   jfds
  1   2   test2
  </code></pre>
  
  <p>Note that in the table the last record is a newer version of the first row. </p>
  
  <p>Does anyone know of an efficient way to query for the latest version of the rows, ans a specific version of records? For instance, a query for <code>rev=2</code> would return the 2, 3 and 4th row (not the replaced 1st row though) while a query for <code>rev=1</code> yields those rows with rev &lt;= 1 and in case of duplicated ids, the one with the higher revision number is chosen (record: 1, 2, 3).</p>
  
  <p>I'm not actually sure if this is even possible in SQL Server...</p>
  
  <p>I would not prefer to return the result in an iterative way.</p>