title: Select most recent row filtered on multiple columns
body: <p>I have an sql table </p>
  
  <pre><code>+------+-----------+-----------+--------+--------------+
  | rowID| location | fk_Item_ID |Balance |Inventory_Date|
  +------+-----------+-----------+--------+--------------+
  | 1    | 1         | p1        |500     |2019-01-10    |
  | 2    | 1         | p2        |450     |2019-01-05    | 
  | 3    | 2         | p2        |500     |2019-01-10    |
  | 4    | 1         | p1        |300     |2019-01-15    |
  | 5    | 1         | p1        |350     |2019-01-16    |
  +------+-----------+-----------+--------+--------------+
  </code></pre>
  
  <p>I'm trying to get write an SQL statement or preferably LINQ to get the most recent row by date then max row number for each part at each location</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM Inventory_Log AS l1
  WHERE 
      rowID IN (
          SELECT MAX(rowID) FROM Inventory_Log 
          WHERE 
              fk_Item_ID = l1.fk_Item_ID 
              AND Inventory_Date = (
                  SELECT MAX(Inventory_Date) 
                  FROM Inventory_Log 
                  WHERE fk_Item_ID = l1.fk_Item_ID
              )
      )
  ORDER by l1.fk_Item_ID
  </code></pre>
  
  <p>Expected results:</p>
  
  <pre><code>+------+-----------+-----------+--------+--------------+
  | rowID| location | fk_Item_ID |Balance |Inventory_Date|
  +------+-----------+-----------+--------+--------------+
  | 2    | 1         | p2        |450     |2019-01-05    | 
  | 3    | 2         | p2        |500     |2019-01-10    |
  | 5    | 1         | p1        |350     |2019-01-16    |
  +------+-----------+-----------+--------+--------------+
  </code></pre>
  
  <p>I'm not sure on how to include multiple locations for the same part
  It will put in to the following class</p>
  
  <pre><code>public class _ItemLocations
  {
     public string Item_ID { get; set; }
     public string Location { get; set; }
     public decimal Qty { get; set; }
  }
  </code></pre>
  
  <p>I haven't tried in LINQ yet because I'm work-shopping it in sql</p>