Skip to content
Advertisement

Retrieve last record in a group based on string – DB2

I have a table with transactional data in a DB2 database that I want to retrieve the last record, per location and product. The date is unfortunately stored as a YYYYMMDD string. There is not a transaction id or similar field I can key in on. There is no primary key.

DATE LOCATION PRODUCT QTY
20210105 A P1 4
20210106 A P1 3
20210112 A P1 7
20210104 B P1 3
20210105 B P1 1
20210103 A P2 6
20210105 A P2 5

I want to retrieve results showing the last transaction per location, per product, so the results should be:

DATE LOCATION PRODUCT QTY
20210112 A P1 7
20210105 B P1 1
20210105 A P2 5

I’ve looked at answers to similar questions but for some reason can’t make the jump from an answer that addresses a similar question to code that works in my environment.

Edit: I’ve tried the code below, taken from an answer to this question. It returns multiple rows for a single location/part combination. I’ve tried the other answers in that question to, but have not had luck getting them to execute.

SELECT * 
FROM t 
WHERE DATE > '20210401' AND DATE in (SELECT max(DATE) 
FROM t GROUP BY LOCATION) order by PRODUCT desc

Thank you!

Advertisement

Answer

You can use ROW_NUMBER(). For example, if your table is called t you can do:

select * 
from (
  select *,
    row_number() over(partition by location, product 
                      order by date desc) as rn
  from t
) x
where rn = 1
9 People found this is helpful
Advertisement