title: How to find a specific row in a table for every record of another table
body: <p>This is what the table looks like -</p>
  
  <hr>
  
  <pre><code>time                    id     code
  
  8/22/1999 12:00:00 AM   0001    A &lt;------------ no
  8/24/2001 6:03:02 AM    0001    A
  6/27/2002 4:45:20 PM    0001    B  
  5/8/2003 9:03:13 AM     0001    B  
  5/8/2003 10:02:34 AM    0001    A &lt;------------ no
  6/9/2008 10:43:03 AM    0001    A
  11/22/2011 3:42:10 PM   0001    A
  4/11/2012 2:03:49 PM    0001    D  
  4/11/2012 2:04:00 PM    0001    D  
  12/6/2017 9:30:17 PM    0001    A &lt;------------ yes
  12/6/2017 9:30:17 PM    0001    A
  12/6/2017 10:06:11 PM   0001    A
  12/7/2017 3:24:58 AM    0001    C  
  1/3/2018  5:02:13 PM    0001    C
  </code></pre>
  
  <p>0001 is an ID from another table. CODE can flip from A to B to C to D any time and in any order. I need to find the most recent occurrence when the code flipped to A from any other code. In this example it happens in the row indicated with "yes". The "no" records are not valid because there is a later occurrence of this situation.</p>
  
  <p>Can't figure out how to go about this. This probably needs a stored procedure, going record by record, maintaining state and figuring out the value.</p>
  
  <p>Edit:</p>
  
  <p>I was wondering if I could somehow add a column called grp like this -</p>
  
  <pre><code>id code
  
  A 1
  A 1
  B 2
  B 2
  A 3
  A 3
  A 3
  D 4
  D 4
  A 5
  A 5
  A 5
  C 6
  C 6
  </code></pre>
  
  <p>Then I would be able to get-</p>
  
  <pre><code>max of the min(grp) group by grp
  </code></pre>