Re: Apache phoenix problem with order by and offset giving duplicate results in paging
I agree with what Thomas recommended though you'd likely want to append the PK to the sort so that you get a consistent result back. That is order by TO_NUMBER(COUNT) desc, PK. As the PK is totally ordered by definition your entire result set is totally ordered. Assuming COUNT is not part of the PK there is no consistent tie breaking amongst values with the same count. This means that the result set you get back may have differences in order causing the behavior you noticed above.
As long as you can do these queries from a single thread, a Cursor could be more performant as you will not have to scan the required data multiple times, but today is similar to doing this in a single query without limit if I recall.