Replace values for each ID and each month in IBM DB2 SQL?

I have about several milion record table in IBM DB2 with 4 columns: yeas, month, personal_number (2 milion), personal_id. The key is that I want to get same table from this one, but I want to fill in personal_id column value from last month (in all previous months). Point is to get 1-1: 1 personal_id relate to 1 personal_number.

Here is my code, but error is that now I have 4 in all monhts (and I need 1,2,3,4 for months)

    With CTE As
(
    SELECT  
            a.YEAR,
            a.MONTH, 
            a.PERSONAL_NUMBER, 
            a.PERSONAL_ID, 
            
            Row_Number() Over (Partition By a.YEAR, a.PERSONAL_NUMBER
                                     Order By a.MONTH DESC) Rn
    FROM
    DWH.PA A
    )
SELECT 
D.YEAR, D.MONTH, D.PERSONAL_NUMBER, D.PERSONAL_ID
FROM CTE D
RIGHT JOIN
(
SELECT
YEAR,
MONTH,
PERSONAL_NUMBER
FROM
DWH.PA
) B
ON (D.PERSONAL_NUMBER = B.PERSONAL_NUMBER)
WHERE D.Rn = 1 
;

Here is the start table:

YEAR	MONTH	PERSONAL_NUMBER	PERSONAL_ID
2020	1	AA	8
2020	2	AA	5
2020	3	AA	5
2020	4	AA	1
2020	1	BB	2
2020	2	BB	2
2020	3	BB	3
2020	4	BB	3

Here is the result (it is not very well result):

YEAR	MONTH	PERSONAL_NUMBER	PERSONAL_ID
2020	4	AA	1
2020	4	AA	1
2020	4	AA	1
2020	4	AA	1
2020	4	BB	3
2020	4	BB	3
2020	4	BB	3
2020	4	BB	3

Here is the what I need:

YEAR	MONTH	PERSONAL_NUMBER	PERSONAL_ID
2020	1	AA	1
2020	2	AA	1
2020	3	AA	1
2020	4	AA	1
2020	1	BB	3
2020	2	BB	3
2020	3	BB	3
2020	4	BB	3

Thank you very much

Answer

Try this:

WITH T (YEAR, MONTH, PERSONAL_NUMBER, PERSONAL_ID) AS
(
VALUES
  (2020, 1, 'AA', 8)
, (2020, 2, 'AA', 5)
, (2020, 3, 'AA', 5)
, (2020, 4, 'AA', 1)
, (2020, 1, 'BB', 2)
, (2020, 2, 'BB', 2)
, (2020, 3, 'BB', 3)
, (2020, 4, 'BB', 3)
)
SELECT 
  T.*
, FIRST_VALUE(PERSONAL_ID) OVER 
  (PARTITION BY YEAR, PERSONAL_NUMBER ORDER BY MONTH DESC) PERSONAL_ID_NEW
FROM T;

The result is:

|YEAR|MONTH|PERSONAL_NUMBER|PERSONAL_ID|PERSONAL_ID_NEW|
|----|-----|---------------|-----------|---------------|
|2020|4    |AA             |1          |1              |
|2020|3    |AA             |5          |1              |
|2020|2    |AA             |5          |1              |
|2020|1    |AA             |8          |1              |
|2020|4    |BB             |3          |3              |
|2020|3    |BB             |3          |3              |
|2020|2    |BB             |2          |3              |
|2020|1    |BB             |2          |3              |