Here is an SQL, which demonstrates the use of Row_Number() function:
with places_table as (
select 'Africa' continent, 'Nigeria' country, 'Lagos' city from dual
union
select 'Africa' continent, 'Nigeria' country, 'Abuja' city from dual
union
select 'Africa' continent, 'Egypt' country, 'Cairo' city from dual
union
select 'Africa' continent, 'Egypt' country, 'Alexandria' city from dual
union
select 'Europe' continent, 'France' country, 'Paris' city from dual
union
select 'Europe' continent, 'France' country, 'Lyon' city from dual
union
select 'Europe' continent, 'Germany' country, 'Frankfurt' city from dual
union
select 'Europe' continent, 'Germany' country, 'Berlin' city from dual
union
select 'Europe' continent, 'Germany' country, 'Stuttgart' city from dual)
select row_number() over (partition by continent, country order by continent, country, city) city_count_per_country,
continent, country, city
from places_table
order by continent, country, city
Result is:
1 Africa Egypt Alexandria
2 Africa Egypt Cairo
1 Africa Nigeria Abuja
2 Africa Nigeria Lagos
1 Europe France Lyon
2 Europe France Paris
1 Europe Germany Berlin
2 Europe Germany Frankfurt
3 Europe Germany Stuttgart
(the counter resets for every country, because of the “partition by” clause)