title: How to select the greatest timestamp in a subquery
body: <p>I have two tables, one with equipment and another that keeps a log of each equipment's status (IN or OUT) and the time of the change. I am trying to make a query that will give me the last known status of every piece of equipment. The query I've come up with forces me to group by status which shows both check IN and check OUT records. I can't figure out how to get this working properly. Any help would be appreciated</p>
  
  <pre><code>SELECT a.[assetid], a.[name], a.[manufacturer], a.[type], b.[status], b.[timestamp]
  FROM .[dbo].[equipment] a
  LEFT JOIN (SELECT assetid, status, MAX(timestamp) as timestamp FROM [dbo].[log] GROUP BY assetid, status) b ON a.assetid=b.assetid
  </code></pre>
  
  <p>Results:</p>
  
  <p><img src="https://i.stack.imgur.com/KruCx.png" alt="http://i57.tinypic.com/5mhjiv.png"></p>
  
  <p>Desired Results:</p>
  
  <p><img src="https://i.stack.imgur.com/KruCx.png" alt="http://i57.tinypic.com/apjp74.png"></p>