Retrieve last record in a group based on string - DB2

Question

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!

Answer 1

You can use lead() to get the last row before a change:

select t.*
from (select t.*,
             lead(date) over (partition by location, product order by date) as next_lp_date,
             lead(date) over (order by date) as next_date
      from t
     ) t
where next_lp_date is null or next_lp_date <> next_date

Answer 2

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

Answer 3

It looks like you just needed to match your keys within the subselect.

SELECT * 
FROM t T1
WHERE DATE > '20210401' 
AND DATE in (SELECT max(DATE) FROM t T2 WHERE T2.Location = T1.Location and T2.Product=T1.Product)