SELECT t.* FROM (SELECT * FROM jobs WHERE `status` IN (0,1,2,3) ORDER BY `status` DESC, job_datetime LIMIT 11, 20) AS t
That's a cut down sample of my sql. It was more complicated, but I've pared it down to this just to see why I was getting some strange numbers for pagination (e.g. 10 records per page).
The above returns 12 records. With the LIMIT clause on the outside, it works as expected, but that's not something I'm able to do as I need to limit the subquery for additional joins later on. Can somebody shed some light on this for me please?
From some research, many say that you can't run LIMIT within a subquery. I've had a look at some workarounds, but they look pretty contrived and a problem to maintain.
