title: SQL Server : adding complex rownumber
body: <p><img src="https://i.stack.imgur.com/1OtWb.png" alt="enter image description here"></p>
  
  <p>I was wondering if someone knows how I can get the missing results from the 2nd column ("As-IS-Rownumber", which are yellow). As example which shows the desired outcome, I added the 1st column("To-Be - Desired"). 
  BTW I work with MS SQL.</p>
  
  <p>I want a rownumber based on the columns KlantID and Repeat. However, when I use <code>partition by</code>, <code>rank</code> or <code>dense_rank</code>, I don't get the desired outcome, because of my last column. </p>
  
  <p>I hope some1 can help me out.</p>
  
  <h2>DDL</h2>
  
  <pre><code>create table tbl (
      ToBeSubRow INT,
      AsIsSubrow INT,
      Rownumber INT,
      KlantID INT,
      Repeat CHAR(3)
  )
  
  insert  tbl (ToBeSubRow, AsIsSubrow, Rownumber, KlantID, Repeat)
  values  (1,1,1,1,'NO'),
          (2,null,2,1,'YES'),
          (3,null,3,1,'YES'),
          (1,1,4,1,'NO'),
          (2,null,5,1,'YES'),
          (1,1,5,2,'NO'),
          (2,null,6,2,'YES'),
          (3,null,7,2,'YES')
  </code></pre>
  
  <p>Thanks 
  Martijn</p>
  
  <p><a href="https://i.stack.imgur.com/9IRGo.png" rel="nofollow noreferrer">enter image description here</a></p>