title: Add order within group and mark whether a row is the last in its group
body: <p>I have a table in an SQL Server database on the following form, sorted according to <code>id</code>.</p>
  
  <pre><code>id     group
  1      10
  17     10
  24     10
  2      20
  16     20
  72     20
  104    20
  8      30
  9      30
  </code></pre>
  
  <p>I would like to select every row grouped according to the row <code>group</code> and add the following information to this table: the order (as sorted) within the group and whether the row is the last row in the group. In other words, something similar to this:</p>
  
  <pre><code>id     group   order  last
  1      10      1      0
  17     10      2      0
  24     10      3      1
  2      20      1      0
  16     20      2      0
  72     20      3      0
  104    20      4      1
  8      30      1      0
  9      30      2      1
  </code></pre>
  
  <p>I've tried fiddling around with <code>ROW_NUMBER</code>, but I'm not all that experienced with SQL Server and I can't get it to work. Does anyone have a suggestion?</p>