title: T-SQL Row Number Restart after N
body: <p>How can I restart my Row_Number after a certain number of rows.</p>
  
  <p>E.g.</p>
  
  <p>How can I turn</p>
  
  <pre><code>ID  RowNumber
  -------------
  100 1
  101 2
  102 3
  125 4
  126 5
  148 6
  149 7
  150 8
  151 9
  </code></pre>
  
  <p>INTO </p>
  
  <pre><code>ID  RowNumber
  -------------
  100 1
  101 2
  102 3
  125 4
  126 1
  148 2
  149 3
  150 4
  151 1
  </code></pre>
  
  <p>Note that there is no way to partition based on the ID, that is actually random.</p>
  
  <p>I have tried to use a recursive CTE and got very close.</p>
  
  <p>This:</p>
  
  <pre><code>DECLARE @MyTable TABLE (ID INT, RowNumber INT)
  
  INSERT INTO @MyTable (ID,RowNumber)
      SELECT 100,1 UNION ALL
      SELECT 101,2 UNION ALL
      SELECT 102,3 UNION ALL
      SELECT 125,4 UNION ALL
      SELECT 126,5 UNION ALL
      SELECT 148,6 UNION ALL
      SELECT 149,7 UNION ALL
      SELECT 150,8 UNION ALL
      SELECT 151,9;
  
  WITH CTE AS 
  (
      SELECT  ID,
              RowNumber ,
              1 AS Steps
      FROM @MyTable   
      WHERE ID = 100
  
      UNION ALL
      SELECT M.ID ,
             M.RowNumber +1,
             CASE WHEN C.Steps = 4 THEN 0 ELSE C.Steps END +1 AS steps
      FROM @MyTable M
          INNER JOIN CTE C ON C.RowNumber = M.RowNumber
  
  )
  SELECT * 
  FROM CTE
  </code></pre>
  
  <p>Returns:</p>
  
  <pre><code>ID  RowNumber
  -------------
  100 1   1
  100 2   2
  101 3   3
  102 4   4
  125 5   1
  126 6   2
  148 7   3
  149 8   4
  150 9   1
  151 10  2
  </code></pre>
  
  <p>Which is very close apart from the first ID.</p>
  
  <p>Any help would be great.</p>
  
  <p>Thanks,</p>
  
  <p>Will</p>