title: How to get the position of a record in a table (SQL Server)
body: <p>Following problem:</p>
  
  <p>I need to get the position of a record in the table. Let's say I have five record in the table:</p>
  
  <pre><code>Name: john doe, ID: 1
  Name: jane doe, ID: 2
  Name: Frankie Boy, ID: 4
  Name: Johnny, ID: 9
  </code></pre>
  
  <p>Now, "Frankie Boy" is in the third position in the table. But How to get this information from the SQL server? I could count IDs, but they are not reliable, Frankie has the ID 4, but is in the third position because the record with the ID '3' was deleted.</p>
  
  <p>Is there a way? I am aware of ROW_RANK but it would be costly, because I need to select basically the whole set first before I can rank row_rank them.</p>
  
  <p>I am using MS SQL Server 2008 R2.</p>