title: Updating table column where one column is max value
body: <p>I am trying to update one column in a series of about 113000 rows in a table. I have a table where there are a MRN, encounter, and admit columns in question. I have come across an issue where an encounter got entered into this table multiple times, but with different MRNS. I want to update a column called 'Message' with a value 'duplicate encounter' but ONLY if that encounter is not the most current admitted date.</p>
  
  <p>For instance</p>
  
  <pre><code>Encounter               MRN                     Admitted
  000000000497            0000097778              2006-01-04 20:26:00
  000000000497            0000092892              2006-03-17 09:51:00
  000000000497            0000003023              2008-08-15 09:50:00
  000000000719            0000103691              2008-10-16 14:45:00
  000000000719            0000048708              2006-05-26 08:04:00
  000000000719            0000024123              2006-01-09 15:43:00
  000000003390            0000099595              2006-03-13 11:30:00
  000000003390            0000079713              2010-04-22 14:40:00
  </code></pre>
  
  <p>For the above info, I would want to update the message column for line 1 and 2 because line 3 is the most current admitted for encounter 000000000497. Lines 5 and 6 for 000000000719 because line 4 is its most current admit date.</p>
  
  <p>I have tried searching through here but couldn't come up with anything. I just didn't know if I needed to create a cursor for this or what. Any help would be VERY much appreciated.  </p>