Put LIMIT in a subquery that sits under IN, ALL, ANY, or SOME, and MySQL hits you with:
ERROR 1235 (42000): This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
LIMIT is already a weak spot in MySQL, so the server blocks this combination. You still need the result set sometimes.
Example rules:
- 5 first-prize winners
- 15 second-prize winners
- Anyone who won first prize cannot win second
You might write something like:
1 | SELECT second_prize_user_id FROM t |
That inner LIMIT is exactly what MySQL forbids. Two workarounds I have used:
Option 1 — wrap in a derived table
1 | SELECT second_prize_user_id FROM t |
Option 2 — join two derived tables
1 | SELECT * |
EXPLAIN on both looked the same to me — filesort and a full table scan. Option 1 is easier to write.
Questions? Leave a comment below or email: newbvirgil@gmail.com![]()
