query against pa_scores_l1 ORDERBYstart_at - 104k+ results but nothing for first ~56200

jamesmanning
Tera Contributor

If I query against /api/now/table/pa_scores_l1?sysparm_query=ORDERBYstart_at the response includes a header of X-Total-Count: 104765 so there should be plenty of results, but the actual body is just {"result":[]}

 

If I sort descending instead with ORDERBYDESCstart_at then it works fine - same number of results, but it shows actual results. 

 

When sorted ascending on start_at, I have to skip ahead 56,200 rows to start getting actual results returned - IOW this query:

 

api/now/table/pa_scores_l1?sysparm_offset=56100&sysparm_limit=100&sysparm_query=ORDERBYstart_at

 

returns {"result":[]}

 

but this query (offset just changing from 56100 to 56200)

 

api/now/table/pa_scores_l1?sysparm_offset=56200&sysparm_limit=100&sysparm_query=ORDERBYstart_at

 

returns actual results (22 results instead of 100, but that's secondary at this point)

 

It seems like there are these phantom (deleted? hidden? ACL?) rows at the start of the total of 104,765 results (over half of them) that don't show up when queried.  Is there a way to modify the query so that these rows that aren't being returned aren't included, and I could page through with sysparm_offset / sysparm_limit and only get actual results?

0 REPLIES 0