SQL get MAX datetime in LEFT JOIN

Question

I am running a query against a table and doing a left join to try and get the record from the left table with the most recent date but it's not picking up the other values relevant to the datetime column (user and notes)

SELECT
    i.customer_sequence,
    i.due_date,
    
    MAX(cn.datetime) as notes_datetime,
    cn.user as notes_user,
    cn.notes as notes_notes
FROM
    billing_invoices i
LEFT JOIN customer_notes cn
    ON i.customer_sequence = cn.customer_seq
WHERE
    cn.type = 'Accounts' AND
    i.customer_sequence <> '0' AND
    i.status = 'Unpaid' AND
    i.directdebit <> 'Y'
GROUP BY
    i.customer_sequence
ORDER BY
    i.due_date DESC

Answer 1

Aggregation is not the solution here. You want the entire row from the joined table, so this suggest filtering instead. If you are running MySQL 8.0, I would recommend window functions:

SELECT *
FROM (
    SELECT
        i.customer_sequence,
        i.due_date,
        ROW_NUMBER() OVER(PARTITION BY i.customer_sequence ORDER BY cn.datetime DESC) rn,
        cn.datetime as notes_datetime,
        cn.user as notes_user,
        cn.notes as notes_notes
    FROM billing_invoices i
    LEFT JOIN customer_notes cn
        ON  i.customer_sequence = cn.customer_seq
        AND cn.type = 'Accounts'
    WHERE
        i.customer_sequence <> '0' AND
        i.status = 'Unpaid' AND
        i.directdebit <> 'Y'
) t
ORDER BY i.due_date DESC    

Note that I moved the condition on the left joined table from the WHERE clause to the ON clause of the join (otherwise, this acts like an inner join).

---

In earlier versions, one option is a correlated subquery:

SELECT
    i.customer_sequence,
    i.due_date,
    cn.datetime as notes_datetime,
    cn.user as notes_user,
    cn.notes as notes_notes
FROM billing_invoices i
LEFT JOIN customer_notes cn
    ON  i.customer_sequence = cn.customer_seq
    AND cn.type = 'Accounts'
    AND cn.datetime = (
        SELECT MAX(cn1.datetime)
        FROM customer_notes cn1
        WHERE i.customer_sequence = cn1.customer_seq AND cn1.type = 'Accounts'
    )
WHERE
    i.customer_sequence <> '0' AND
    i.status = 'Unpaid' AND
    i.directdebit <> 'Y'