Geek Answers Handbook

Is there a way around the LIMIT in a subquery in MySQL?

Here is my original request ...

SELECT `id` FROM `properties` LIMIT 10, 20

The LIMIT is for pagination.

Now I have to get everything as before, but I need to get only a third of the lines where the condition is present.

I came up with this just by pressing LIMIT 30 before I figured out how to do this (common lines correspond to / 3) * 2.

 SELECT `id` FROM `properties` WHERE `id` NOT IN (SELECT `id` FROM `properties` WHERE `vendor` = "abc" ORDER BY RAND() LIMIT 30) LIMIT 10, 20

MySQL said ...

1235 - This version of MySQL does not yet support "LIMIT and IN / ALL / ANY / SOME subquery"

I think I cannot use LIMIT in a subquery.

So this is a multiprocess, but all related ...

  • Is there a workaround for LIMIT in a subquery?
  • Is it possible to select 1/3 of the matched rows with MySQL?
  • Should I include this in 2 queries or just select all and delete the lines not required in PHP?
+7
php mysql mysql-error-1235
source share
3 answers

If your version of MySQL does not support this, you have 2 options:

  • Upgrade It's always fun, and it's usually best to be in the latest version.
  • Breaking your subquery with php. Take the identifiers, then format the results in a comma-separated string.
+4
source share

Too bad I'm late, this worked for me:

  SELECT p.id FROM properties p LEFT JOIN (SELECT t.id FROM PROPERTIES t WHERE t.vendor = 'abc' ORDER BY RAND() LIMIT 30) x ON x.id = p.id WHERE x.id IS NULL LIMIT 10, 20
+15
source share

MySQL supports LIMIT in a subquery ... but MySQL does NOT support using IN / NOT IN with a subquery.

Honestly, I really don’t know what you are trying to accomplish.

+1
source share

More articles:


All Articles