Mysql Paging With multiple ORDER BY

Question

I have some issue with mysql pager.

Count all rows is 164.
pro_type - can be in 3 different integer values;
pro - integer value;
cr_date - some date.

Here is my queries:

SELECT id FROM table WHERE f1='1' AND f2='0' AND uid=43 ORDER BY pro_type DESC, pro DESC, cr_date DESC LIMIT 100;

SELECT id FROM table WHERE f1='1' AND f2='0' AND uid=43 ORDER BY pro_type DESC, pro DESC, cr_date DESC LIMIT 100 OFFSET 100;

Second query result has duplicate rows from first query result.
Both results do not contain one or few rows.
The number of rows of the first query 100 and second is 64, so result count is ok.
Query with limit 200 return all valid rows(164 not duplicated).

Maybe someone knows, what is the problem with ORDER BY & LIMIT OFFSET in that case?

Thank you.

Try SELECT DISTINCT id

M Khalid Junaid
– M Khalid Junaid

2015-05-11 10:44:10 +00:00
Commented May 11, 2015 at 10:44 — M Khalid Junaid
– M Khalid Junaid, Commented May 11, 2015 at 10:44
Can you make a sqlfiddle that demonstrates the problem?

Barmar
– Barmar

2015-05-11 10:45:14 +00:00
Commented May 11, 2015 at 10:45 — Barmar
– Barmar, Commented May 11, 2015 at 10:45

Gordon Linoff · Accepted Answer · 2015-05-11 10:47:14Z

3

Basically, what is likely to be happening is that the data has duplicate values of pro_type, pro, cr_date. When you use order by and the keys are the same, MySQL does not guarantee the ordering of results with the same key values. In other words, the sort is not "stable".

Assuming that id is unique, the solution is simply to add it to the order by clause so the sort keys are unique:

ORDER BY pro_type DESC, pro DESC, cr_date DESC, ID
------------------------------------------------^
LIMIT 100;

answered May 11, 2015 at 10:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Sergey Nikolaev Over a year ago

Thank you, now I'm understand. "cr_date DESC, ID" - solve a problem.

Collectives™ on Stack Overflow

Mysql Paging With multiple ORDER BY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related