title: DENSE_RANK breaking sequentially when changed
body: <p>The <code>grp</code> column should be represent a block partitioned by <code>GroupId, RouteId</code> and ordered <code>LengthStart</code> where as you travel through the blocks if the locationId remains the same the <code>grp</code> is the same. A break in the sequence creates a new <code>grp</code> </p>
  
  <pre><code>+---------+----------+--------------+------------+-------------+------+
  | GROUPID |  ROUTEID |  LENGTHSTART |  LENGTHEND |  LOCATIONID |  GRP |
  +---------+----------+--------------+------------+-------------+------+
  |     1   | A        | 0            | 1          | 1           | 1    |
  |     1   | A        | 1            | 2          | 1           | 1    |
  |     1   | A        | 2            | 3          | 2           | 2    |
  |     1   | A        | 3            | 4          | 1           | 3    |
  |     2   | A        | 2            | 3          | 2           | 4    |
  |     1   | B        | 2            | 3          | 2           | 5    |
  |     1   | A        | 4            | 5          | 1           | 3    |
  +---------+----------+--------------+------------+-------------+------+
  </code></pre>
  
  <p>My search on this problem led me to this solution: <a href="https://stackoverflow.com/questions/34429903/dense-rank-according-to-particular-order">DENSE_RANK according to particular order</a></p>
  
  <p>My attempt at making the <code>grp</code> calculation: </p>
  
  <pre><code>SELECT *, ROW_NUMBER() OVER (ORDER BY GroupId, RouteId, LengthStart) - ROW_NUMBER() OVER (PARTITION BY GroupId, RouteId, LocationId ORDER BY GroupId, RouteId, LengthStart) AS grp
  FROM mytable
  </code></pre>
  
  <p>I tried to adopt that solution such that I could have more levels of partitioning and it works in really basic use-cases (like the one showcased above but in complicated scenarios its failing).</p>
  
  <p>I don't really understand completely why two row_number() are subtracting and how it works out but it worked really well in the simple example. </p>
  
  <p>I tried other approaches uses <code>LAG</code> but just fail to understand how to take the logic and apply it.</p>
  
  <p>Here is a fiddle with the more complicated scenerio:</p>
  
  <p><a href="https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=3704dfe8583b0dd020b189184d149cb7" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=3704dfe8583b0dd020b189184d149cb7</a></p>
  
  <p>You can see one of the many mistakes I've been seeing highlighted here: <a href="https://i.stack.imgur.com/t8Y75.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/t8Y75.png" alt="enter image description here"></a></p>