Oracle 11g Release 2

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

create table guestn (gid int, name varchar2(10));
create table visitn (gid int, name varchar2(10));
insert into guestn (gid, name) values (123, 'Anne');
1 rows affected

insert into guestn (gid, name) values (456, 'Peter');
1 rows affected

insert into visitn (gid, name) values (123, 'Chicago');
1 rows affected

insert into visitn (gid, name) values (456, 'New York');
1 rows affected

insert into visitn (gid, name) values (456, 'Boston');
1 rows affected

select * from (
  SELECT g.GID, g.Name, COUNT(*) as cnt
  FROM VisitN v JOIN
     GuestN g
     ON v.GID = g.GID
  GROUP BY g.GID, g.Name
  order by cnt desc
) x
where rownum = 1


GID
NAME
CNT


456
Peter
2