title: order by in inner join while updating table
body: <p>I need to update a table by inner joining another table. Below is the example.</p>
  
  <pre><code>Employee table:
  
  role        name    Time create
  manager     abc      2013
  sales       def      2014
  manager     xyz      2015
  sales       dje      2015
  
  Currentteam:
  
  role      name
  manager 
  sales
  </code></pre>
  
  <p>I need to update <code>Currentteam</code> table with name from employee table, but I need to update recent name meaning for the <code>manager</code> role. We have two names in the <code>Employee</code> table, but I need to update only one name which is recent&nbsp;&mdash; that is, <code>XYZ</code>. I tried to do something like the below, but it is not allowing <code>order by</code> in the sub query.</p>
  
  <pre><code>Update currentteam
  Set name=temp.name
  From currentteam
  Inner join (select * from employee order by timecreate) as temp on temp.role=currentteam
  </code></pre>
  
  <p>Please advise!!!</p>