SQL Server 2017

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

create table foo(
  mi varchar(5)
, month_no integer
, year integer
, remarks varchar(5)
);
insert into foo(mi,month_no,year,remarks) values
  ('001',1,2019,'FULL')
, ('001',2,2019,null)
, ('001',3,2019,'FULL')
, ('002',1,2019,null)
, ('002',2,2019,null)
, ('002',3,2019,'FULL')
, ('003',1,2019,null)
, ('003',2,2019,'FULL')
, ('003',3,2019,null)
;
9 rows affected

select mi,month_no,year,remarks
     , newremarks =
       max(remarks) over (partition by mi order by month_no desc ,year desc)
from foo
order by mi,month_no,year;


mi
month_no
year
remarks
newremarks


001
1
2019
FULL
FULL

001
2
2019
null
FULL

001
3
2019
FULL
FULL

002
1
2019
null
FULL

002
2
2019
null
FULL

002
3
2019
FULL
FULL

003
1
2019
null
FULL

003
2
2019
FULL
FULL

003
3
2019
null
null


Warning: Null value is eliminated by an aggregate or other SET operation.

mi	month_no	year	remarks	newremarks
001	1	2019	FULL	FULL
001	2	2019	null	FULL
001	3	2019	FULL	FULL
002	1	2019	null	FULL
002	2	2019	null	FULL
002	3	2019	FULL	FULL
003	1	2019	null	FULL
003	2	2019	FULL	FULL
003	3	2019	null	null