sql - Mysql Paging With multiple ORDER BY -

i have issue mysql pager.

• count rows 164.
• pro_type - can in 3 different integer values;
• pro - integer value;
• cr_date - date.

here queries:

select id table f1='1' , f2='0' , uid=43 order pro_type desc, pro desc, cr_date desc limit 100;  select id table f1='1' , f2='0' , uid=43 order pro_type desc, pro desc, cr_date desc limit 100 offset 100; 

• second query result has duplicate rows first query result.
• both results not contain 1 or few rows.
• the number of rows of first query 100 , second 64, result count ok.
• query limit 200 return valid rows(164 not duplicated).

maybe knows, problem order & limit offset in case?

thank you.

basically, happening data has duplicate values of pro_type, pro, cr_date. when use order by , keys same, mysql not guarantee ordering of results same key values. in other words, sort not "stable".

assuming id unique, solution add order by clause sort keys unique:

order pro_type desc, pro desc, cr_date desc, id ------------------------------------------------^ limit 100;