title: How do I filter SQL Query to get the required rows
body: <p>Hey this is my first Stack Overflow Question so if I have not asked it in the best way please let me know and I can add more info.</p>
  <p>Context:</p>
  <p>I have a sql table with Four Columns Called:</p>
  <p>ACC_ID, BLOCK_CATEGORY, START_DATE, VALUE</p>
  <p>The Columns are:
  (Number), (Number), (Date), (Number)</p>
  <p>The table records all changes made to an account as a new row and the ACC_ID is a unique columns associated to the account. The START_DATE is the date the change was made. For this we can ignore the Value Column.</p>
  <p>I have a requirement to run a query to understand when all the accounts changed to the current BLOCK_CATEGORY that they are on. The problem I am facing is that the Block Category is a number 1-8 and they may have been on the same BLOCK_CATEGORY before but we need to know when it changed to it this time?</p>
  <p>Here is some sample Data to help you Understand (For my sample the Date Format is DD/MM/YYYY):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ACC_ID</th>
  <th>BLOCK_CATEGORY</th>
  <th>START_DATE</th>
  <th>Value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1001</td>
  <td>7</td>
  <td>14/08/2022</td>
  <td>5</td>
  </tr>
  <tr>
  <td>1001</td>
  <td>2</td>
  <td>16/08/2022</td>
  <td>5</td>
  </tr>
  <tr>
  <td>1001</td>
  <td>7</td>
  <td>17/08/2022</td>
  <td>10</td>
  </tr>
  <tr>
  <td>1001</td>
  <td>7</td>
  <td>19/08/2022</td>
  <td>10</td>
  </tr>
  <tr>
  <td>1002</td>
  <td>4</td>
  <td>14/08/2022</td>
  <td>3</td>
  </tr>
  <tr>
  <td>1002</td>
  <td>3</td>
  <td>15/08/2022</td>
  <td>3</td>
  </tr>
  <tr>
  <td>1002</td>
  <td>3</td>
  <td>17/08/2022</td>
  <td>9</td>
  </tr>
  <tr>
  <td>1003</td>
  <td>1</td>
  <td>14/08/2022</td>
  <td>10</td>
  </tr>
  <tr>
  <td>1003</td>
  <td>1</td>
  <td>17/08/2022</td>
  <td>13</td>
  </tr>
  <tr>
  <td>1004</td>
  <td>3</td>
  <td>14/08/2022</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1005</td>
  <td>7</td>
  <td>14/08/2022</td>
  <td>11</td>
  </tr>
  <tr>
  <td>1005</td>
  <td>2</td>
  <td>16/08/2022</td>
  <td>34</td>
  </tr>
  <tr>
  <td>1005</td>
  <td>3</td>
  <td>19/08/2022</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1005</td>
  <td>7</td>
  <td>21/08/2022</td>
  <td>12</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The Desired end result of this is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ACC_ID</th>
  <th>BLOCK_CATEGORY</th>
  <th>START_DATE</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1001</td>
  <td>7</td>
  <td>17/08/2022</td>
  </tr>
  <tr>
  <td>1002</td>
  <td>3</td>
  <td>15/08/2022</td>
  </tr>
  <tr>
  <td>1003</td>
  <td>1</td>
  <td>14/08/2022</td>
  </tr>
  <tr>
  <td>1004</td>
  <td>3</td>
  <td>14/08/2022</td>
  </tr>
  <tr>
  <td>1005</td>
  <td>7</td>
  <td>21/08/2022</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I hope through the above example and question you understand the need. Please ask any questions you have.</p>
  <p>The current query I am using gives me the below incorrect result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ACC_ID</th>
  <th>BLOCK_CATEGORY</th>
  <th>START_DATE</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1001</td>
  <td>7</td>
  <td>14/08/2022</td>
  </tr>
  <tr>
  <td>1002</td>
  <td>3</td>
  <td>15/08/2022</td>
  </tr>
  <tr>
  <td>1003</td>
  <td>1</td>
  <td>14/08/2022</td>
  </tr>
  <tr>
  <td>1004</td>
  <td>3</td>
  <td>14/08/2022</td>
  </tr>
  <tr>
  <td>1005</td>
  <td>7</td>
  <td>14/08/2022</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Here is the Query I am using. How can we run a query to give the correct desired result which is when it changed to the current BLOCK_CATEGORY.</p>
  <pre><code>SELECT *
  
  FROM (
  
  SELECT ACC_ID,
  BLOCK_CATEGORY,
  START_DATE,
  ROW_NUMBER() OVER (PARTITION BY acc_id order by start_date DESC) RowNum
  
  FROM
  (
  SELECT ACC_ID,
  BLOCK_CATEGORY,
  MIN(START_DATE) 'START_DATE'
  
  FROM [dbo].[ACCOUNTCHANGES]
  WHERE
  BLOCK_CATEGORY IS NOT NULL
  GROUP BY ACC_ID,BLOCK_CATEGORY
  ) A
  ) B
  WHERE B.RowNum = 1
  </code></pre>