title: SQL Server increment rank Partition By split groupings of duplicate values
body: <p>Given a table like the following:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Part</th>
  <th>Batch</th>
  <th>TransactionDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1001</td>
  <td>2022-06-10 13:25:00</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1001</td>
  <td>2022-06-11 14:00:00</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1002</td>
  <td>2022-06-12 07:25:00</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1002</td>
  <td>2022-06-12 08:25:00</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1002</td>
  <td>2022-06-12 09:25:00</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1001</td>
  <td>2022-06-13 10:21:00</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1003</td>
  <td>2022-06-10 11:20:00</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1004</td>
  <td>2022-06-11 12:20:00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>How can I rank by part and batch while ensuring the date/time order of TransactionDate column?</p>
  <ul>
  <li>The table should be ordered by Part followed by TransactionDate</li>
  <li>When there is a new part, the rank should restart from 1</li>
  <li>Each block of consecutive part/batch should receive the same rank</li>
  <li>When a batch changes from row to row within the same part (even if batch is a previous one from some row above), the rank should be incremented by 1</li>
  <li>the number of rows in each consecutive block of part/batch numbers can vary</li>
  </ul>
  <p>So I should get the following table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Part</th>
  <th>Batch</th>
  <th>TransactionDate</th>
  <th>Rank</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1001</td>
  <td>2022-06-10 13:25:00</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1001</td>
  <td>2022-06-11 14:00:00</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1002</td>
  <td>2022-06-12 07:25:00</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1002</td>
  <td>2022-06-12 08:25:00</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1002</td>
  <td>2022-06-12 09:25:00</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1001</td>
  <td>2022-06-13 10:21:00</td>
  <td>3</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1003</td>
  <td>2022-06-13 11:20:00</td>
  <td>1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1004</td>
  <td>2022-06-13 12:20:00</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I was thinking of using dense rank in a partition such as the below:</p>
  <p><code>DENSE_RANK() OVER (PARTITION BY Part, Batch ORDER BY TransactionDate)</code></p>
  <p>However, the above combines all part and batch combinations into subsequent rows and then orders by transaction date. Therefore, it ranks all part/batch combinations (of 1 and 1001 respectively) as 1. But I need it to respect the transaction date order as in the table above.</p>
  <p>Any help on a query to do the above would be appreciated.</p>
  <p>EDIT: the number of rows in each consecutive block of part/batch numbers can vary.</p>