Select most recent row filtered on multiple columns

I have an sql table

+------+-----------+-----------+--------+--------------+
| 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    |
+------+-----------+-----------+--------+--------------+

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

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

Expected results:

+------+-----------+-----------+--------+--------------+
| 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    |
+------+-----------+-----------+--------+--------------+

I’m not sure on how to include multiple locations for the same part It will put in to the following class

public class _ItemLocations
{
   public string Item_ID { get; set; }
   public string Location { get; set; }
   public decimal Qty { get; set; }
}

I haven’t tried in LINQ yet because I’m work-shopping it in sql

Answer

In SQL Server, use ROW_NUMBER():

SELECT * FROM (
    SELECT 
        i.*, 
        ROW_NUMBER() OVER(PARTITION BY location, fk_Item_ID ORDER BY Inventory_Date DESC, rowID DESC) rn
    FROM Inventory_Log i
) x WHERE rn = 1

NB: it is unclear to me what kind of sorting you expect with rowID, since you stated that you want records sorted by Inventory_Date: I used it as a second sort criteria (hence it will break top ties on Inventory_Date if needed).

Demo on DB Fiddle:

rowID | location | fk_Item_ID | Balance | Inventory_Date      | rn
----: | -------: | :--------- | ------: | :------------------ | :-
    2 |        1 | p2         |     450 | 05/01/2019 00:00:00 | 1 
    3 |        2 | p2         |     500 | 10/01/2019 00:00:00 | 1 
    5 |        1 | p1         |     350 | 16/01/2019 00:00:00 | 1