DB2 Query to get Latest Data

Question

I need to query a DB2 table to get the latest data stored in the table by each unique Sup_Num and CONTAINER_CODE combination. The problem I'm having is that I can't figure out how to get only the data with the most recent date (INSERTED_DT). I tried using the Max() function on the INSERTED_DT field, but it gave me the exact same result I get without using the Max() function. I didn't see anything like this when I searched for Max() function usage examples so I'm hoping someone on here could help.

The query I am using is this:

Select Distinct
    SupLookup.SUPPLIER_NO concat SupLookup.SUPPLIER_LOCATION AS Sup_Num,
    SupLookup.CONTAINER_CODE,
    AllocationType.ALLOC_TYPE_DESC,
    Allocation.ALLOC_QTY,
    Allocation.SAFE_STOCK_QTY,
    Allocation.ALLOC_QTY + Allocation.SAFE_STOCK_QTY AS CALC_TOT_ALLOC_REQ_QTY,
    SupLookup.ALLOC_REQ_QTY,
    SupLookup.TOT_ALLOC_REQ_QTY,
    SupLookup.DISC_QTY,
    SupLookup.FILL_PERCENT,
    MAX(SupLookup.INSERTED_DT) as Insert_DT

From RCX.RXSAL1 Allocation
    Inner Join rcx.RXALT1 AllocationType on Allocation.ALLOC_TYPE_ID = AllocationType.ALLOC_TYPE_ID
    Left Join rcx.RXPIR1 SupLookup on Allocation.SUPPLIER_ID = SupLookup.SUPPLIER_ID And allocation.CONTAINER_TYPE_ID = SupLookup.CONTAINER_TYPE_ID

Where Allocation.PLANT_ID= '50000036'

Group by 
    SupLookup.SUPPLIER_NO concat SupLookup.SUPPLIER_LOCATION,
    SupLookup.CONTAINER_CODE,
    AllocationType.ALLOC_TYPE_DESC,
    Allocation.ALLOC_QTY,
    Allocation.SAFE_STOCK_QTY,
    Allocation.ALLOC_QTY + Allocation.SAFE_STOCK_QTY,
    SupLookup.ALLOC_REQ_QTY,
    SupLookup.TOT_ALLOC_REQ_QTY,
    SupLookup.DISC_QTY,
    SupLookup.FILL_PERCENT

ORDER BY Sup_Num ASC

What I'm getting looks like this:

But what I want is for it to pick out just the most recent date for each combination of Sup_Num and Container_Code like this:

-EDIT-

The simple max/group by sub query here fails in this instance (server timeout). Someone suggested it at one point but has since removed the post.

Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Clockwork-Muse, Apr 17 '18 at 16:21

score 1 · Accepted Answer · answered Apr 16 '18 at 20:21

Your query does not work because FILL_PERCENT is more less unique making up a separate group for each row. Alternatively to the subquery mentioned by scaisEdge you could also use the ROW_UMBER function - order by INSERTED_DT descending and only retrieve the first row of each SupLookup.SUPPLIER_NO, SupLookup.CONTAINER_CODE combination.

This would look like this:

with temp as (
    Select 
        SupLookup.SUPPLIER_NO concat SupLookup.SUPPLIER_LOCATION AS Sup_Num,
        SupLookup.CONTAINER_CODE,
        AllocationType.ALLOC_TYPE_DESC,
        Allocation.ALLOC_QTY,
        Allocation.SAFE_STOCK_QTY,
        Allocation.ALLOC_QTY + Allocation.SAFE_STOCK_QTY AS CALC_TOT_ALLOC_REQ_QTY,
        SupLookup.ALLOC_REQ_QTY,
        SupLookup.TOT_ALLOC_REQ_QTY,
        SupLookup.DISC_QTY,
        SupLookup.FILL_PERCENT,
        SupLookup.INSERTED_DT,
        row_number() over (partition by SupLookup.SUPPLIER_NO, SupLookup.CONTAINER_CODE order by SupLookup.INSERTED_DT desc) as rownum

    From RCX.RXSAL1 Allocation
        Inner Join rcx.RXALT1 AllocationType on Allocation.ALLOC_TYPE_ID = AllocationType.ALLOC_TYPE_ID
        Left Join rcx.RXPIR1 SupLookup on Allocation.SUPPLIER_ID = SupLookup.SUPPLIER_ID And allocation.CONTAINER_TYPE_ID = SupLookup.CONTAINER_TYPE_ID

    Where Allocation.PLANT_ID= '50000036'
) 
SELECT Sup_Num, CONTAINER_CODE, ALLOC_TYPE_DESC, ALLOC_QTY, SAFE_STOCK_QTY, CALC_TOT_ALLOC_REQ_QTY, ALLOC_REQ_QTY, TOT_ALLOC_REQ_QTY, DISC_QTY, FILL_PERCENT,INSERTED_DT
  FROM temp
WHERE rownum = 1

This seems to be close, but if a supplier has multiple locations with the same container code rownum indexes as if they were the same. For example just looking at Supllier # | Location | Container | Date, 021320 | 01 | AP | 4/16/2018 and 021320 | 01 | AP | 4/16/2018 will index as 1 and 2 but then 021320 | 07 | AP | 4/16/2018 will be 3 for some reason. — 110SidedHexagon, Apr 17 '18 at 17:20
I figured it out, I just needed to add SupLookup.SUPPLIER_LOCATION to the partition and it works perfectly. Thanks for the help! — 110SidedHexagon, Apr 17 '18 at 17:37

DB2 Query to get Latest Data

1 Answers1