Postgres 9.6

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

select version();


version


PostgreSQL 9.6.20 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table test (id int, country varchar, year int, rank int);



insert into test values
(1,  'Austria', 2019,    1),
(2,  'Austria', 2018 ,  null),
(3,  'Austria', 2017,   null),
(4,  'Austria', 2016,   null),
(5,    'Spain', 2019,    2),
(6,    'Spain', 2018,   null),
(7,    'Spain', 2017,   null),
(8,    'Spain', 2016,   null),
(9,  'Belgium', 2019,    3),
(10, 'Belgium', 2018,   null),
(11, 'Belgium', 2017,   null),
(12, 'Belgium', 2016,   null)
12 rows affected

with cte as (
select distinct on (country) * from test order by country, year desc
)

select t1.id,t1.country,t1.year,t2.rank from test t1 left join cte t2 on t1.country=t2.country 


id
country
year
rank


1
Austria
2019
1

2
Austria
2018
1

3
Austria
2017
1

4
Austria
2016
1

5
Spain
2019
2

6
Spain
2018
2

7
Spain
2017
2

8
Spain
2016
2

9
Belgium
2019
3

10
Belgium
2018
3

11
Belgium
2017
3

12
Belgium
2016
3


with cte as (
select distinct on (country) * from test order by country, year desc
)
update test set rank=cte.rank from cte
where test.country=cte.country
12 rows affected

select * from test


id
country
year
rank


1
Austria
2019
1

2
Austria
2018
1

3
Austria
2017
1

4
Austria
2016
1

5
Spain
2019
2

6
Spain
2018
2

7
Spain
2017
2

8
Spain
2016
2

9
Belgium
2019
3

10
Belgium
2018
3

11
Belgium
2017
3

12
Belgium
2016
3

id	country	year	rank
1	Austria	2019	1
2	Austria	2018	1
3	Austria	2017	1
4	Austria	2016	1
5	Spain	2019	2
6	Spain	2018	2
7	Spain	2017	2
8	Spain	2016	2
9	Belgium	2019	3
10	Belgium	2018	3
11	Belgium	2017	3
12	Belgium	2016	3