mariadb alternative to outer apply or lateral?

What I wanted was to use CROSS APPLY, but I guess that doesn’t exist in mysql. The alternative I’ve read is LATERAL. Well, I’m using mariadb 10.3 and I guess that doesn’t exist either. The ticket table contains an id that’s referenced by the ticket_id column in the note table. A ticket can have many notes, I’m trying to list all tickets with their most recent note date (post_date). How could I write the query below for mariadb?

SELECT t.*, n.post_date
FROM ticket t,
LATERAL (
    SELECT note.post_date FROM note WHERE t.id = note.ticket_id ORDER BY note.post_date DESC LIMIT 1
) n;

Example table structure:

Ticket

note

I did find an open jira ticket from people asking for mariadb to support lateral.

>Solution :

From what I read, LATERAL will not be supported in MariaDB until version 11. But we can just as easily use ROW_NUMBER here, which is supported:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY post_date DESC) rn
    FROM note
)

SELECT t.*, n.post_date
FROM ticket t
INNER JOIN cte n
    ON n.ticket_id = t.id
WHERE n.rn = 1;

If you wanted a close translation of your current lateral join, then use:

SELECT t.*,
    (SELECT n.post_date
     FROM note n
     WHERE t.id = note.ticket_id
     ORDER BY n.post_date DESC
     LIMIT 1)
FROM ticket t;