1

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

id subject
1 stuff
2 more

note

id post_date ticket_id
1 1
2 1
3 2
4 1
5 2

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

Phaelax z
  • 1,814
  • 1
  • 7
  • 19
  • 1
    For those interested in the tickets for this: https://jira.mariadb.org/browse/MDEV-19078 and https://jira.mariadb.org/browse/MDEV-6373 – Jens Schauder Apr 14 '23 at 11:53

1 Answers1

0

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360