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?