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!

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

Advertisement

Answer