title: Increment Row Number on Group
body: <p>I am working on a query for SQL Server 2005 that needs to return data with two 'index' fields. The first index 't_index' should increment every time the 'shade' column changes, whilst the second index increments within the partition of the values in the 'shade' column:</p>
  
  <pre><code>t_index s_index shade
  1       1       A
  1       2       A
  1       3       A
  1       4       A
  1       5       A
  2       1       B
  2       2       B
  2       3       B
  2       4       B
  2       5       B
  </code></pre>
  
  <p>To get the s_index column I am using the following:</p>
  
  <pre><code>Select ROW_NUMBER() OVER(PARTITION BY [shade] ORDER BY [shade]) as s_index
  </code></pre>
  
  <p>My question is how to get the first index to only increment when the value in the 'shade' column changes?</p>