MySQL error 1064 near "LIMIT": the subquery syntax MySQL does not have (and the JOIN that replaces it)

verbatim errorERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1) AS latest ORDER BY created_at DESC' at line 3

Problem

A "latest status per order" query that worked in Postgres failed on the MySQL 5.7 box:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1) AS latest ORDER BY created_at DESC' at line 3

The query was a lateral-style correlated subquery with LIMIT, written the way Postgres or SQLite would accept it.

Root cause

Two MySQL-specific constraints collided:

1. MySQL (before 8.0.14) has no LATERAL derived tables, so the natural "for each row, take top-N" formulation cannot be expressed that way. 2. Even without LATERAL, older MySQL versions error on certain LIMIT usages inside IN (...) subqueries ("This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'") — a different 1064-adjacent error that people conflate with this one.

The query was trying to do per-group top-1 with a tool that did not have the syntax for it.

✅ verified against mysql 8.0.14

fix preview — first 3 of 9 lines (sql), truncated:

SELECT o.id, o.total, latest.status, latest.created_at
FROM orders o
JOIN LATERAL (
… 6 more lines in the fix

🔒 the fix — including 3 code blocks — is members-only. $1/mo unlocks everything.