Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


 create table table1(id int,loan_age int,  ori_upb int,  ori_rate numeric,  ltv numeric);
 
insert into table1 values( 1, 360, 1500,  4.5, 0.6 );
insert into table1 values(  2, 360, 2000,  3.8, 0.5 );
1 rows affected

1 rows affected


create table table2(id int,month varchar, cur_upb int, cur_rate numeric, status int);
 
insert into table2 values(1,'01',1400,4.5,0);
insert into table2 values(1,'02',1300,4.5,  0);
insert into table2 values(1,'03',1200,4.5,  1);
insert into table2 values(2,'01',2000,3.8,  0);
insert into table2 values(2,'02',1900,3.8,  0);
insert into table2 values(2,'03',1900,3.8,  1);
insert into table2 values(2,'04',1900,3.8,  2);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

 create table table3(id int,loan_age int,ori_upb int,ori_rate numeric,ltv numeric,  payoffmonth varchar, payoffupb int, payoffrate numeric, lastStatus int, modification date)
select distinct on (t1.id) t1.id, t1.loan_age, t1.ori_upb,t1.ori_rate,t1.ltv,t2.month,t2.cur_upb, t2.cur_rate, t2.status from table1 t1 inner join table2 t2 on t1.id=t2.id order by t1.id , t2.month desc


id
loan_age
ori_upb
ori_rate
ltv
month
cur_upb
cur_rate
status


1
360
1500
4.5
0.6
03
1200
4.5
1

2
360
2000
3.8
0.5
04
1900
3.8
2


insert into table3 (id,  loan_age,  ori_upb,  ori_rate,  ltv,  payoffmonth, payoffupb,  payoffrate, lastStatus )
select distinct on (t1.id) t1.id, t1.loan_age, t1.ori_upb,t1.ori_rate,t1.ltv,t2.month,t2.cur_upb, t2.cur_rate, t2.status from table1 t1 inner join table2 t2 on t1.id=t2.id order by t1.id , t2.month desc
2 rows affected

select * from table3


id
loan_age
ori_upb
ori_rate
ltv
payoffmonth
payoffupb
payoffrate
laststatus
modification


1
360
1500
4.5
0.6
03
1200
4.5
1
null

2
360
2000
3.8
0.5
04
1900
3.8
2
null

id	loan_age	ori_upb	ori_rate	ltv	payoffmonth	payoffupb	payoffrate	laststatus	modification
1	360	1500	4.5	0.6	03	1200	4.5	1	null
2	360	2000	3.8	0.5	04	1900	3.8	2	null