title: Sql Nested query with MAX
body: <p>I have a table with 4 columns</p>
  
  <p>For every id on Col2 and for every value=c on Col1, I need to update Column DataNew with the most recent date (from Column Data) found on the record that has that same ID and a value of either a and b on Col1</p>
  
  <p>Here is an example with DataNew after the update.</p>
  
  <pre><code>Col1|Col2|Data      |DataNew
  ----|----|----------|-----------
  a   |x   |12/05/2013|
  b   |x   |12/04/2013|
  c   |x   |12/09/2013|12/04/2013
  b   |x   |12/08/2013|
  a   |f   |12/06/2014|
  b   |f   |12/04/2014|
  c   |f   |12/09/2014|12/06/2014
  d   |f   |12/08/2014|
  </code></pre>
  
  <p>(12/05/2013 = 5th of Dec)</p>
  
  <pre><code>UPDATE TAT_Test
  SET DataNew = 
  (select top 1 MAX(Data)
  from TAT_Test 
  where Col1 IN ('a','b')
  ) 
  where Col1 IN ('c')
  </code></pre>
  
  <p>This is my attempt but not sure how to show inside the nested query that I'm looking for Col1=current record</p>
  
  <p>Thanks!</p>