Paging is one of those things that you wish there was more support for, but you want real control over. I’ve found a number of ways to do it, each has advantages and drawbacks. These are the two best solutions I’ve found. I have to list two, as one works only when sorting by unique db fields, and the other is slower but can be sorted by any field.
For data sorted by a unique field (eg. primary key or unique Name field): A More Efficient Method for Paging Through Large Result Sets
For data that needs to be sorted by a non-unique field, a different approach is needed. There might be a way to adapt the previous method… but I haven’t been able to yet.
Easy, yet fast, SQL paging solution
The problem seems to centre on there being no way to distinguish between rows when sorting by a non-unique field. It is possible to store the primary key, however the sorting is complex.
For example, the first item of a page is test2, below. but since we sort by date, test1 is selected instead.
This can be prevented by using a primary key. However, the sorting cannot be maintained, as any subsequent rows with Name > selected row will not sort lower. The goal is to find a way that retrieves items in the exact same order, and allows the selection to start from any row.