MYSQL – Join only the lastest row from the second table

I have patients table and appointments table. I want to query appointments from today and join patient info on the query.

This

SELECT a.PatientID, a.DoctorID, a.DT, 
p.id, p.Name 
FROM appointment a 
JOIN Patient p 
ON p.id = a.PatientID 
WHERE DATE(DT) = CURDATE() 
GROUP BY p.id 
ORDER BY a.DT DESC

Works, but it shows the first row it finds (usually the older one) not respecting the order by (to show only the latest by patient). What am I missing here?

UPDATE: Sorry, I should write it more properly.

Patient table:

id Name
1  Jonathan
2  Helena

Appointment table

PatientID DoctorID DT
1         1        2021-08-27 09:30
2         1        2021-08-27 10:00
1         1        2021-08-27 11:00

If I query as I have it, the return will be

  1         1        2021-08-27 09:30
  2         1        2021-08-27 10:00

Instead of

2         1        2021-08-27 10:00
1         1        2021-08-27 11:00

Answer

Use window function for retrieving the patient wise latest date record. Then join patient table. If all patient info is needed then use LEFT JOIN instead of INNER JOIN.

-- MySQL (v5.8)
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;

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=944f2dd72105bc9fb06b1a6b5fd837f2

For lower version where window functions are not supported.

-- MySQL(v5.6)
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;

Please check from url https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=d665ffe9f3d09df23513ad50be31b388