Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
ORA-00936: missing expression

create table my_user
( username varchar2(200) not null,
  name varchar2(200),
  surname varchar2(200),
  CONSTRAINT user_pk PRIMARY KEY (username)
);
create table my_address
( id number,
  username varchar2(200),
  address varchar2(200),
  CONSTRAINT fk_users FOREIGN KEY (username) REFERENCES my_user(username)
);
insert into my_user(username, name, surname) values ('user1', 'name', 'surname');
1 rows affected

insert into my_user(username, name, surname) values ('user2', 'name', 'surname');
1 rows affected

insert into my_address values (1, 'user1', 'road 11');
1 rows affected

insert into my_address values (2, 'user1', 'road 22');
1 rows affected

insert into my_address values (3, 'user2', 'road 33');
1 rows affected

insert into my_address values (4, 'user2', 'road 44');
1 rows affected

insert into my_address values (5, 'user2', 'road 55');
1 rows affected

select u.*, a.id, a.address 
from my_user u
join
(
  select *
  from my_address ma
  where id in (select max(id) from my_address  group by username)
) a on a.username = u.username;


USERNAME
NAME
SURNAME
ID
ADDRESS


user2
name
surname
5
road 55

user1
name
surname
2
road 22


select u.*, a.id, a.address 
from my_user u
join
(
  select ma.*, row_number() over (partition by username order by id desc) as rn
  from my_address ma
) a on a.username = u.username and a.rn = 1;


USERNAME
NAME
SURNAME
ID
ADDRESS


user1
name
surname
2
road 22

user2
name
surname
5
road 55


select u.*, a.id, a.address 
from my_user u
cross apply
(
  select *
  from my_address ma
  where ma.username = u.username
  order by ma.id desc
  fetch first row only
) a;


USERNAME
NAME
SURNAME
ID
ADDRESS


user1
name
surname
2
road 22

user2
name
surname
5
road 55