title: Select the latest record for each category linked available on an object
body: <p>I have a tblMachineReports with the columns: Status(varchar),LogDate(datetime),Category(varchar), and MachineID(int).</p>
  
  <p>I want to retrieve the latest status update from each category for every machine, so in effect getting a snapshot of the latest statuses of all the machines unique to their MachineID.</p>
  
  <p>The table data would look like<br></p>
  
  <p>Category - Status - MachineID - LogDate <br>
  cata - status1 - 001 - date1<br>
  cata - status2 - 002 - date2<br>
  catb - status3 - 001 - date2<br>
  catc - status2 - 002 - date4<br>
  cata - status3 - 001 - date5<br>
  catc - status1 - 001 - date6<br>
  catb - status2 - 001 - date7<br>
  cata - status2 - 002 - date8<br>
  catb - status2 - 002 - date9<br>
  catc - status2 - 001 - date10<br>
  <br><br>
  Restated, I have multiple machines reporting on multiple statuses in this tblMachineReports. All the rows are created through inserts, so their will obviously be duplicate entries for machines as new statuses come in. None of the columns can be predicted, so I can't do any ='some hard coded string' comparisons in any part of the select statement.<br></p>
  
  <p><br>For the sample table I provided, the desired results would look like:<br><br>
  Category - Status - MachineID - LogDate <br>
  catc - status2 - 002 - date4<br>
  cata - status3 - 001 - date5<br>
  catb - status2 - 001 - date7<br>
  cata - status2 - 002 - date8<br>
  catb - status2 - 002 - date9<br>
  catc - status2 - 001 - date10<br></p>
  
  <p>What would the select statement look like to achieve this, getting the latest status for each category on each machine, using MS SQL Server 2008? I have tried different combinations of subqueries combined with aggregate MAX(LogDates)'s, along with joins, group bys, distincts, and what-not, but have yet to find a working solution.</p>