Mysql LIMIT returning unexpected rows

Question

SELECT *
FROM users
ORDER BY highscore DESC
LIMIT 5 OFFSET 5

and

SELECT *
FROM users
ORDER BY highscore DESC
LIMIT 5 OFFSET 10

returning the same result. And they are different than the records when I omit the LIMIT clause! I searched the community. There are similar questions, but they are of no help.

EDIT: Here are the table data-

Is there any restriction to prevent users from having the same highscore? — Álvaro González
– Álvaro González, Commented Jul 3, 2017 at 10:17
No restrictions. I should also mention that many of them have the same high score. — Shuvo Sarker
– Shuvo Sarker, Commented Jul 3, 2017 at 10:18
Please check the result of SELECT * FROM users ORDER BY highscore DESC LIMIT 15. It's possible that values are same. — Keyur Panchal
– Keyur Panchal, Commented Jul 3, 2017 at 10:22
Keyur Panchal. Yes, the high score values are the same for them. They are all 0. — Shuvo Sarker
– Shuvo Sarker, Commented Jul 3, 2017 at 10:24

Gordon Linoff · Accepted Answer · 2017-07-03 10:37:19Z

2

Presumably, the issue is that you have ties for highscore. When you have ties, then MySQL orders the rows with the same value in an arbitrary and indeterminate way. Even two runs of the same query can result in different orderings.

Why? The reason is simple. There is no "natural" order for sorting keys with the same value. SQL tables represent unordered sets.

To make the sorting stable, include a unique id as the last key in the ORDER BY:

SELECT u.*
FROM users u
ORDER BY u.highscore DESC, u.userId
LIMIT 5 OFFSET 5;

Then, when you fetch the next 5 rows, they will be different.

answered Jul 3, 2017 at 10:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Mysql LIMIT returning unexpected rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related