Is there a workaround for using LIMIT in a subquery in MySQL?

Question

Here is my original query...

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

The LIMIT condition is for pagination.

Now, I have to get all like before, but I need to get only a third of rows where a condition is present.

I came up with this, just throwing LIMIT 30 in before I figured out how to do (total rows matched / 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 doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I guess I can't use LIMIT in a subquery.

So this is a multi question but all related...

• Is there a workaround for LIMIT in subquery?
• Can I select a 1/3 of matched rows with MySQL?
• Do I need to turn this into 2 queries, or just select all and unset the rows not required in PHP?

Answer 1

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

To be honest I really don't know what you are trying to accomplish.

Answer 2

If your version of MySQL doesn't support that then you have 2 options:

• Upgrade. Always fun, and it's generally best to be on the latest version.
• Break out your subquery using php. Grab the ids, then format the results into a comma separated string.

Answer 3

Sorry 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