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
, tmp.DoctorID
, tmp.DT
FROM Patient p
INNER JOIN (SELECT @row_no := IF(@prev_val = t.PatientID, @row_no + 1, 1) AS row_number
, @prev_val := t.PatientID PatientID, t.DoctorID, t.DT
FROM Appointment t,
(SELECT @row_no := 0) x,
(SELECT @prev_val := 0) y
ORDER BY t.PatientID, t.DT DESC) tmp
ON p.id = tmp.PatientID
AND tmp.row_number = 1
ORDER BY tmp.DT;
| PatientID | patient_name | DoctorID | DT |
|---|---|---|---|
| 2 | Helena | 1 | 2021-08-27 10:00:00 |
| 1 | Jonathan | 1 | 2021-08-27 11:00:00 |