2013 REST api $skiptoken with $orderby returns wrong number of results

Question

I noticed some discrepancies in the number of results when getting paged list items when I $orderBy different fields.

I set up a test list with 196 items, the below snippet will get items in batches of 50 and when all done will log the total number of items returned.

If I $orderBy : Id or completely omit $orderBy, I get the correct overall item count (196).

If I $orderBy : Group, the overall item count will be 144. (In this case, the Group column contains non-unique values, if the Group column contained unique values, it would get the correct count, 196) The API generated __next looks OK, but clearly something is going wrong with the paging logic.

Any suggestions? Am I right to just pass __next to the next request, without the original params? They seems to be already included in __next.

(function () {

    var url = '../_api/web/lists/getByTitle(\'Test\')/items',
        getItemsDfd = $.Deferred(),
        params = {
            '$select': 'Title',
            '$orderBy': 'Id', // ok
            //'$orderBy': 'Group', // bad
            '$top': 50
        },
        count = 0;

    function getItems(url, params) {

        $.ajax({
            url: url,
            type: 'GET',
            data: params,
            headers: {
                'accept': 'application/json;odata=verbose'
            },
            success: function (data) {

                console.log('Url requested', decodeURIComponent(this.url));

                count += data.d.results.length;
                if (data.d.__next) {
                    // for the next batch, use the
                    // url the api returns
                    getItems(data.d.__next);
                } else {
                    // when __next is undefined
                    // we have reached the end
                    getItemsDfd.resolve(count);
                }

            }
        });

    }

    // kick off batch operation
    getItems(url, params);

    getItemsDfd.done(function (count) {
        console.log('Item count', count);
    });

}());

Answer 1

I also encountered this problem.
It seems like SharePoint (2019) is having troubles with paging in combination with $orderby.
In my case it was a list of names and there were 3 items with "Sophie" as title.
The list is ordered by title and the first page ends after the first 2 "Sophie" items.
The next page starts with the first "Sophie" despite it already being in the first page.

Additionally sorting by ID did fix the problem for me.

let d1 = await $.ajax({url:_spPageContextInfo.webAbsoluteUrl+ "/_api/lists/getbytitle('Names')/items?$orderby=Title asc",headers:{ accept: "application/json;odata=verbose" }})
let d2 = await $.ajax({url:d1.d.__next,headers:{ accept: "application/json;odata=verbose" }})
console.log(d1.d.results.map(_=>_.Title+_.ID))
console.log(d2.d.results.map(_=>_.Title+_.ID))
/*results in 
...
98: "Sophie92"
99: "Sophie56"
---- next page
0: "Sophie92"
1: "Sophie 137"
...
*/

d1 = await $.ajax({url:_spPageContextInfo.webAbsoluteUrl+ "/_api/lists/getbytitle('Names')/items?$orderby=Title,ID asc",headers:{ accept: "application/json;odata=verbose" }})
d2 = await $.ajax({url:d1.d.__next,headers:{ accept: "application/json;odata=verbose" }})
console.log(d1.d.results.map(_=>_.Title+_.ID))
console.log(d2.d.results.map(_=>_.Title+_.ID))
/*results in 
...
98: "Sophie56"
99: "Sophie92"
---- next page
0: "Sophie137"
1: "Theresa133"
...
*/