title: Issue with the TOP 1 query
body: <p>Is it possible to achieve next thing without using views, but just one single query? I have two tables:</p>
  
  <p>TableA->TanbleB (1-many) ON TableA.Id = TableB.TableAId</p>
  
  <p>I need to update one field in Table A (TableA.Field1) for records in TableA that satisfy condition on one field in tableA (WHERE TableA.Field2=SomeValue)
  .
  TableA.Field1 will be updated from TableB with value that is last inserted (last inserted value in related records to TableA).</p>
  
  <p>I will put an example:</p>
  
  <pre><code>UPDATE TableA a SET Field1 = (SELECT TOP 1 b.Feild1 * b.Field2 FROM TableB b WHERE b.TableAId = a.id) WHERE field2 = 1
  </code></pre>
  
  <p>I know Above example doesn't work, but I have many ways tried using INNER JOIN and failed. I had an idea to use something like this:</p>
  
  <pre><code>UPDATE TableA INNDER JOIN ( SELECT ... FROM TABLE B) ON TABLEA.Id= TableB.TableAId SET ....
  </code></pre>
  
  <p>But the 2ns query should return 1 record for each DISTINCT TableAId, but only the last inserted.</p>
  
  <p>I hope I am making some sense here.</p>
  
  <p>Thanks in advance.</p>