DB2查询获取最新数据：按Sup_Num与CONTAINER_CODE组合取最新记录

Got it, let's break down why your current use of MAX(INSERTED_DT) isn't working as expected, then walk through reliable solutions to get the latest storage data for each unique Sup_Num + CONTAINER_CODE pair.

The Problem with Your Current Approach #

When you just add MAX(INSERTED_DT) to a query grouped by Sup_Num and CONTAINER_CODE, DB2 doesn’t automatically tie that max date to the rest of the fields in the record. For example, if your query looks like this:

SELECT Sup_Num, CONTAINER_CODE, MAX(INSERTED_DT), Storage_Column1, Storage_Column2
FROM your_table
GROUP BY Sup_Num, CONTAINER_CODE

DB2 will return the max date for each group, but Storage_Column1 and Storage_Column2 will pull arbitrary values from the group—not the ones associated with that latest date. That’s why your results look identical to when you didn’t use MAX(): you’re not actually fetching the full latest record.

Solution 1: Use ROW_NUMBER() Window Function (Recommended) #

This is the cleanest, most efficient way to get exactly the latest record per group. Window functions let you rank records within each Sup_Num + CONTAINER_CODE pair by date, then pick the top-ranked one.

WITH ranked_records AS (
    SELECT 
        Sup_Num,
        CONTAINER_CODE,
        -- List all your storage data columns here
        Storage_Temp,
        Storage_Location,
        INSERTED_DT,
        -- Rank records per group, latest date first
        ROW_NUMBER() OVER (
            PARTITION BY Sup_Num, CONTAINER_CODE 
            ORDER BY INSERTED_DT DESC
        ) AS record_rank
    FROM your_table_name
)
SELECT 
    Sup_Num,
    CONTAINER_CODE,
    Storage_Temp,
    Storage_Location,
    INSERTED_DT
FROM ranked_records
WHERE record_rank = 1;

• PARTITION BY splits your data into groups based on Sup_Num and CONTAINER_CODE
• ORDER BY INSERTED_DT DESC ensures the most recent record gets a rank of 1
• WHERE record_rank = 1 filters to only keep the latest record per group

If there’s a chance multiple records in the same group have the exact same latest INSERTED_DT, you can add a secondary sort (like a primary key) to pick a consistent winner:

ORDER BY INSERTED_DT DESC, Your_Primary_Key DESC

Solution 2: Subquery with MAX() + Join (For Older DB2 Versions) #

If your DB2 version doesn’t support CTEs (common table expressions), you can use a subquery to first get the latest date per group, then join back to the original table to fetch the full matching record.

SELECT t1.*
FROM your_table_name t1
INNER JOIN (
    -- Get the latest date for each Sup_Num + CONTAINER_CODE pair
    SELECT 
        Sup_Num,
        CONTAINER_CODE,
        MAX(INSERTED_DT) AS latest_insert_date
    FROM your_table_name
    GROUP BY Sup_Num, CONTAINER_CODE
) t2 
    ON t1.Sup_Num = t2.Sup_Num 
    AND t1.CONTAINER_CODE = t2.CONTAINER_CODE 
    AND t1.INSERTED_DT = t2.latest_insert_date;

Note: If multiple records share the same latest date for a group, this will return all of them. If you need only one, combine this with a DISTINCT or add a filter on a unique column.

Quick Check #

Make sure INSERTED_DT is a timestamp or date type (not a string) — if it’s stored as text, MAX() might not sort correctly. Double-check that the column name is spelled exactly right (case sensitivity matters in some DB2 configurations).

内容的提问来源于stack exchange，提问作者110SidedHexagon