nick nick - 4 months ago 14
SQL Question

How can I remove duplicate array elements by value and keep the latest entry?

Here is my query, the inner query gives me an array sorted by date, but if I use group by it keeps a random duplicate rather than the first or last one. In my case, there may be multiple appointments with the same patient id, but I only need the latest. Is there a way I can either use php to keep the first occurence of a duplicate value and remove the rest while keeping the date sort or do it in a query?

$sql = 'SELECT *
FROM (
SELECT a.patient_id, a.appointment_datetime, b.first_name, b.last_name, b.email
FROM '.TABLE_APPOINTMENTS.' a, '.TABLE_PATIENTS.' b
WHERE doctor_id='.$doctor_id.' AND a.patient_id = b.id
ORDER BY appointment_datetime desc) AS t
GROUP BY patient_id';


output:

Array ( [0] => Array ( [patient_id] => 1 [appointment_datetime] =>
[1] => Array ( [patient_id] => 2 [appointment_datetime] => )
[2] => Array ( [patient_id] => 3 [appointment_datetime] => )
[3] => Array ( [patient_id] => 5 [appointment_datetime] =>
[4] => Array ( [patient_id] => 20 [appointment_datetime] => 2016-06-29 17:30:00 ) )


output of inner query:

Array ( [0] => Array ( [patient_id] => 20 [appointment_datetime] => 2016-07-14 17:00:00
) [1] =>Array ( [patient_id] => 20 [appointment_datetime] => 2016-07-05 23:00:00 )
[2] => Array ( [patient_id] => 20 [appointment_datetime] => 2016-06-29 17:30:00 )
[3] => Array ( [patient_id] => 20 [appointment_datetime] =>
[4] =>Array ( [patient_id] => 20 [appointment_datetime] => )
[5] = Array ( [patient_id] => 20 [appointment_datetime] => )
[6] => Array ( [patient_id] => 1 [appointment_datetime] => )
[7] => Array ( [patient_id] => 2 [appointment_datetime] => )
[8] => Array ( [patient_id] => 3 [appointment_datetime] => )
[9] => Array ( [patient_id] => 5 [appointment_datetime] => ) )


solution:

$sql = 'SELECT a.patient_id, max(a.appointment_datetime), b.first_name, b.last_name, b.email
FROM '.TABLE_APPOINTMENTS.' a, '.TABLE_PATIENTS.' b
WHERE doctor_id='.$doctor_id.' AND a.patient_id = b.id
GROUP BY a.patient_id';

Answer

Try specifying that you want the latest entry directly in your query:

$sql = 'SELECT * 
    FROM (
        SELECT a.patient_id, MAX(a.appointment_datetime), b.first_name, b.last_name, b.email 
            FROM '.TABLE_APPOINTMENTS.' a, '.TABLE_PATIENTS.' b 
            WHERE doctor_id='.$doctor_id.' AND a.patient_id = b.id 
            ORDER BY appointment_datetime desc) AS t 
    GROUP BY patient_id';

Notice the MAX() added to the query.

Good luck!