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.

Edited by diafol

5 Years
Discussion Span
Last Post by pritaeas

LIMIT 11,20 means start at 11 (zero based), get 20 records (if any)... Without data, it'll be hard to tell. Query appears legit.

Edited by pritaeas

Votes + Comments
Yes, I fell down - again.... :)

Oh God. Yes I just realised. Having a very senior moment after a late night pub quiz. I even tried to reconfig the thing with GROUP_CONCAT and still came up with 12 records. So realisation finally hit me and you've just confirmed it to the world - before I could wipe the thread. Ha ha. Cheers p - can't believe it (hangs head in shame)....

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.