title: Row_Number() OVER PARTITION BY based on a value in column
body: <p><a href="https://i.stack.imgur.com/vb74x.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vb74x.jpg" alt="enter image description here"></a></p>
  
  <p>Table Name: L</p>
  
  <p>I'm currently working with a dataset that aims to offer insights on customer support cases. In the example above, you can see the progression of case 123376 from reopened to answered. </p>
  
  <p>When I use the partition function <code>ROW_NUMBER() OVER (PARTITION BY L.CaseNumber, L.[Status], L.NextDate ORDER BY L.[Date] ASC)</code> then I get the <code>Row_Number</code> as above. However, once the case status is answered and the next date is <code>NULL</code>, then I want to delete the rows for that case that come after, meaning I only want to keep the first row for Next Date <code>NULL</code> (in a single case number) and delete the others. Is there a way to have row numbers over partition so that the result looks like this or any other approach? <a href="https://i.stack.imgur.com/TSUiL.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TSUiL.jpg" alt="enter image description here"></a></p>