Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Player(PlayerID int primary key, Role varchar (20),Nationname varchar(20))
insert into Player values(1, 'midfield', 'Brazil')
1 rows affected

insert into Player values(2, 'midfield', 'Brazil')
1 rows affected

insert into Player values(3, 'midfield', 'Italy')
1 rows affected

insert into Player values(4, 'striker', 'Croatia')
1 rows affected

insert into Player values(5, 'midfield', 'Croatia')
1 rows affected

insert into Player values(6, 'midfield', 'Croatia')
1 rows affected

select *
from (SELECT nationname, COUNT(role) midfield
      FROM player
      WHERE role = 'midfield'
      GROUP BY nationname)
where rownum = 1
order by midfield desc


NATIONNAME
MIDFIELD


Brazil
2


SELECT nationname, COUNT(role) midfield
FROM player
WHERE role = 'midfield'
GROUP BY nationname
having count(role) in (select max(count(role)) from player WHERE role = 'midfield' group by nationname)


NATIONNAME
MIDFIELD


Brazil
2

Croatia
2