LIMIT posts conditionaly

Databases
1

I have a table with two columns in one table one is posted(date) and other hidden(boolean). I want to select all posts that are not hidden with limit and offset plus all other posts that are potest on a same date with the one selected from the not hidden posts.

I’ve tried something like this:


(   
    select * FROM item WHERE hidden = 0 LIMIT 20 OFFSET 0
)
UNION
(

    select * FROM item as i1 WHERE i1.hidden = 1 AND i1.posted in (select i2.posted FROM item as i2 WHERE i2.hidden = 0 LIMIT 20 OFFSET 0)

) ORDER BY posted DESC

And i get this error:
This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

Althorugh even if it would work this query makes 3 selects is there a way to optimize and get the same result? With less queries?

2

since each of the two parts of your UNION returns only 20 rows, just run them separately, and combine/sort the results with your application language (php or whatever)

3

Well the second one doesn`t return 20 it has limit of 20 in a subquery which actually is not allowed in mysql

4

yes, i understand, but you can run the subquery, grab the 20 posted values, and use them in an IN list for the outer query

you gots to do it in steps if you can’t upgrade your server

5

Maybe using ajax to load the hidden when they are needed will be less stressful for the server. Thank you for your time i really appreciate your answers.