title: SQLServer SQL query with a row counter
body: <p>I have a SQL query, that returns a set of rows:</p>
  
  <pre><code>SELECT id, name FROM users where group = 2
  </code></pre>
  
  <p>I need to also include a column that has an incrementing integer value, so the first row needs to have a 1 in the counter column, the second a 2, the third a 3 etc</p>
  
  <p>The query shown here is just a simplified example, in reality the query could be arbitrarily complex, with several joins and nested queries.</p>
  
  <p>I know this could be achieved using a temporary table with an autonumber field, but is there a way of doing it within the query itself ?</p>