Last record per item per group

Question

Simplified problem: In a table "table1" we have columns: RowID, ItemID, BranchID, RoomID, Date, Qty

I am trying to retrieve the last Qty of an ItemID in each RoomID in each BranchID.

Once I get this going the plan is to JOIN table1 to ItemIDTable, BranchIDTable, RoomIDTable to get names for IDs.

By using MAX(Date) I got as far as getting Qty for ItemID that are only in one RoomID, however if an ItemID is in multiple RoomID the function MAX(Date) returns the latest record among all rooms, while I need the latest for each room.

Data set for ItemID = 50 and BranchID = 4:

+--------+----------+--------+-----+---------------------+------------+------------+--------------+
| ItemID | BranchID | RoomID | Qty |        Date         | ItemIDName | RoomIDNAme | BranchIDName |
+--------+----------+--------+-----+---------------------+------------+------------+--------------+
|     50 |        4 |      1 |   7 | 2019-12-12 13:30:15 | ItemA      | RoomB      | BranchB      |
|     50 |        4 |      2 |   5 | 2019-12-12 13:30:20 | ItemA      | RoomA      | BranchB      |
|     50 |        4 |      2 |   8 | 2019-12-12 13:30:25 | ItemA      | RoomA      | BranchB      |
+--------+----------+--------+-----+---------------------+------------+------------+--------------+

Results I get (it selected latest out of two RoomIDs):

+--------+----------+--------+-----+---------------------+------------+------------+--------------+
| ItemID | BranchID | RoomID | Qty |        Date         | ItemIDName | RoomIDNAme | BranchIDName |
+--------+----------+--------+-----+---------------------+------------+------------+--------------+
|     50 |        4 |      2 |   8 | 2019-12-12 13:30:25 | ItemA      | RoomA      | BranchB      |
+--------+----------+--------+-----+---------------------+------------+------------+--------------+

Expected (latest Qty for each RoomID):

+--------+----------+--------+-----+---------------------+------------+------------+--------------+
| ItemID | BranchID | RoomID | Qty |        Date         | ItemIDName | RoomIDNAme | BranchIDName |
+--------+----------+--------+-----+---------------------+------------+------------+--------------+
|     50 |        4 |      1 |   7 | 2019-12-12 13:30:15 | ItemA      | RoomB      | BranchB      |
|     50 |        4 |      2 |   8 | 2019-12-12 13:30:25 | ItemA      | RoomA      | BranchB      |
+--------+----------+--------+-----+---------------------+------------+------------+--------------+

The query itself:

SELECT     
    table1.ItemID, 
    table1.BranchID, 
    table1.RoomID, 
    table1.Qty, 
    table1.Date, 
    ItemIDTable.ItemIDName, 
    RoomIDTable.RoomIDName, 
    BranchIDTable.BranchIDName
FROM         
    table1 INNER JOIN
    ItemIDTable ON table1.ItemID = ItemIDTable.ItemID INNER JOIN
    RoomIDTable ON table1.RoomID = RoomIDTable.RoomID INNER JOIN
    BranchIDTable ON table1.BranchID = BranchIDTable.BranchID
WHERE     
    (table1.Date IN
        (
            SELECT     MAX(Date)
            FROM          table1
            WHERE      (ItemID = table1.ItemID) AND (BranchID = table1.BranchID)
        )
    )
ORDER BY 
    table1.ItemID

I've tried to shorten and simplify titles to make it more readable. A would appreciate any insights into this, whether to make this query work or use a better method.

Answer 1

I am trying to retrieve the last Qty of an ItemID in each RoomID in each BranchID.

Your intent to filter with a correlated subquery looks to me like a good idea. However, your WHERE clause does not exactly does what you want: it seems to be missing a condition on RoomID:

WHERE  
    (table1.Date IN
        (
            SELECT     MAX(Date)
            FROM          table1
            WHERE      (ItemID = table1.ItemID) AND (BranchID = table1.BranchID)
        )
    )

Also, the condition should be rewritten to use an equality instead of IN, since the subquery returns maximum one record anyway. Please note that most parentheses here are superfluous. Finally, I would suggest to use table aliases to disambiguate the column names in the subquery.

Consider:

SELECT     
    t1.ItemID, 
    t1.BranchID, 
    t1.RoomID, 
    t1.Qty, 
    t1.Date, 
    i.ItemIDName, 
    r.RoomIDName, 
    b.BranchIDName
FROM         
    table1 t1
    INNER JOIN ItemIDTable i ON t1.ItemID = i.ItemID
    INNER JOIN RoomIDTable r ON t1.RoomID = r.RoomID
    INNER JOIN BranchIDTable ON t1.BranchID = b.BranchID
WHERE t1.Date = (
    SELECT MAX(t11.Date)
    FROM   table1 t11
    WHERE 
        t11.ItemID = t1.ItemID
        AND t11.BranchID = t1.BranchID
        AND t11.RoomID = t1.RoomID
    )
ORDER BY t1.ItemID

Answer 2

You could use the ROW_NUMBER() window function:

DECLARE @Tab TABLE (ItemID INT, BranchID INT, RoomID INT, Qty INT,Dt datetime,ItemIDName varchar(10),RoomIDNAme varchar(10),BranchIDName varchar(10))
insert @tab
values
(50,4,1,7,'2019-12-12 13:30:15','ItemA','RoomB','BranchB'),
(50,4,2,5,'2019-12-12 13:30:20','ItemA','RoomA','BranchB'),
(50,4,2,8,'2019-12-12 13:30:25','ItemA','RoomA','BranchB')

 SELECT *
 FROM(
     SELECT row_number() over(partition by itemid, branchid, roomid order by dt desc) rn, *
     FROM @Tab
     ) t
 WHERE t.rn = 1

Or with your query:

SELECT *
FROM(
    SELECT ROW_NUMBER() OVER(PARTITION BY table1.ItemID, table1.BranchID, table1.RoomID, ORDER BY table1.Date DESC) rn
        table1.ItemID, 
        table1.BranchID, 
        table1.RoomID, 
        table1.Qty, 
        table1.Date, 
        ItemIDTable.ItemIDName, 
        RoomIDTable.RoomIDName, 
        BranchIDTable.BranchIDName
    FROM         
        table1 INNER JOIN
        ItemIDTable ON table1.ItemID = ItemIDTable.ItemID INNER JOIN
        RoomIDTable ON table1.RoomID = RoomIDTable.RoomID INNER JOIN
        BranchIDTable ON table1.BranchID = BranchIDTable.BranchID
    WHERE     
        (table1.Date IN
            (
                SELECT     MAX(Date)
                FROM          table1
                WHERE      (ItemID = table1.ItemID) AND (BranchID = table1.BranchID)
            )
        )
  ) t
 WHERE t.rn = 1
ORDER BY 
    table1.ItemID