Overlapping results on mysql query when using limit/offset

Question

I'm new to databases, and im not sure why this isnt working.

My objective is to get all the users in a database except the first one. I would also like this to be sortable, so it contains ORDER BY.

I finally got pagination to work, but the query is returning overlapping results on different pages

QUERY:

SELECT * FROM `users` as `User`  WHERE '1'='1' AND `User`.`id` > '1'  ORDER BY User.active ASC LIMIT 0, 10

i get these back (here is their IDs in the order i got) 32,18,19,16,15,14,13,12,11,2

Now with the LIMIT part set to 10,100, i get these back 32,20,19,2,11,12,13,14,15,15

I'm not sure why i get overlapping results. Help?

Thanks :)

Ask me if any query!!

Jain
– Jain

2014-02-09 19:56:23 +00:00
Commented Feb 9, 2014 at 19:56 — Jain
– Jain, Commented Feb 9, 2014 at 19:56

Jain · Accepted Answer · 2014-02-09 19:52:52Z

1

Try this:

SELECT * FROM users as User WHERE '1'='1' AND User.id > 1 ORDER BY User.id ASC LIMIT 0,10

OR TRY something simple:

SELECT * FROM users WHERE users.id > 1 LIMIT 0,10

answered Feb 9, 2014 at 19:52

Jain

1,24910 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lili Jason Over a year ago

Thanks Jain, the first query does in fact stops the overlapping of results. How do i go by ordering by other fields in the table? I guess i have to find a way to order by first then take the limit of that?

Jain Over a year ago

@above!! Yes order by is always works before limit applied. And order by can be set of any of your columns value.

Lili Jason Over a year ago

Thanks. So i combined two simpler queries and it seems to be working SELECT * FROM ( SELECT * FROM users as User WHERE '1'='1' AND User.id > '1' ORDER BY User.active ASC ) AS Outter LIMIT 0, 10

Collectives™ on Stack Overflow

Overlapping results on mysql query when using limit/offset

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related