title: How to remove all the duplicate records except the last occurence in a table
body: <p>I have a interim table without any primary key and identity. I need to check one of the columns (branch_ref) value for duplicate entries and should mark the flag as 'D' if the branch_ref is same for more than one record except the last occurrence in the table. How can we do this?</p>
  
  <p>Actual data as stored in table.<a href="https://i.stack.imgur.com/UwJra.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UwJra.jpg" alt="Actual data as stored in table"></a></p>
  
  <pre><code>select branch_name,branch_reference,address_1,zip_cd,null as flag_val  FROM Branch_Master
  </code></pre>
  
  <p>As per above table, I need all flag to be updated as ‘D’ except for 6th (brach_reference=9910) and 16th record (branch_reference=99100 and zip_cd=612).</p>
  
  <p>When I use row_number function to identify the duplicates order gets changed.</p>
  
  <pre><code>SELECT branch_name,branch_reference,address_1,zip_cd,flag_val, ROW_NUMBER() OVER(PARTITION BY branch_reference ORDER BY branch_reference) RID
            FROM Branch_Master
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/3p7M9.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3p7M9.jpg" alt="How flag is getting updated"></a>
  Am using below query to update flag_val and its updating wrong records. </p>
  
  <pre><code>;WITH CTE AS 
          (
            SELECT branch_name,branch_reference,address_1,zip_cd,flag_val,  ROW_NUMBER() OVER(PARTITION BY branch_reference ORDER BY branch_reference) RID
            FROM Branch_Master
            WHERE flag_val IS NULL
          )
          UPDATE C1 SET flag_val = 'D' 
          FROM CTE C1 
          LEFT OUTER JOIN (SELECT branch_reference, max(RID) MRID FROM CTE GROUP BY branch_reference) C2 
              ON C1.branch_reference=C2.branch_reference and C1.RID=C2.MRID
          WHERE C2.branch_reference IS NULL
  </code></pre>