By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Patient (id int, name varchar(10));
insert into Patient values (1, 'Jonathan'), (2, 'Helena');
create table Appointment (PatientID int, DoctorID int, DT datetime);
insert into Appointment values (1, 1, '2021-08-27 09:30'),(2, 1, '2021-08-27 10:00')
,(1, 1, '2021-08-27 11:00');
SELECT p.id PatientID
, p.Name patient_name
, t.DoctorID
, t.DT
FROM Patient p
INNER JOIN (SELECT PatientID
, DoctorID
, DT
, ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY DT DESC) row_num
FROM Appointment) t
ON p.id = t.PatientID
AND t.row_num = 1
ORDER BY t.DT;
| PatientID | patient_name | DoctorID | DT |
|---|---|---|---|
| 2 | Helena | 1 | 2021-08-27 10:00:00 |
| 1 | Jonathan | 1 | 2021-08-27 11:00:00 |