Member Avatar for diafol 
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
  • 2 Contributors
  • 3 Replies
  • 182 Views
  • 30 Minutes Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for 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
commented: Yes, I fell down - again.... :) +14
Member Avatar for diafol

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)....

Member Avatar for pritaeas

before I could wipe the thread

I almost feel sorry for replying :D

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.