> On Feb 1, 2017, at 7:18 AM, Richard Hipp <[email protected]> wrote:
>
> See also https://www.sqlite.org/rowvalue.html#scrolling_window_queries
> <https://www.sqlite.org/rowvalue.html#scrolling_window_queries>
This approach comes with a major caveat that’s not mentioned in the text: the
data set cannot contain rows that have the same ‘order by’ values. From the
example:
SELECT * FROM contacts
WHERE (lastname,firstname) > (?1,?2)
ORDER BY lastname, firstname
LIMIT 7;
If the lastname and firstname on the bottom row of the previous screen
are bound to ?1 and ?2,
then the query above computes the next 7 rows.
This makes the assumption that (lastname, firstname) is unique in the table,
i.e. the there are no two people with the same last and first names. That’s
pretty likely in a personal address book, very unlikely in a phone book!
If there are duplicates, then if one page of results ends in the middle of a
run of duplicates, the next page will skip the rest of the duplicates. That’s
data loss. Sad!
The best solution is to add criteria to the ordering/comparison to make every
row unique. For example, use (lastname, firstname, customerid). If you don’t
have a unique value to use, you could always use `rowid`.
If that isn’t feasible (you have a no-rowid table?) you have to fall back to
using “>=“ instead of “>” in the test, and then manually skipping the initial
row(s) that already appeared in the last page. (And this in turn will fall if
there’s a run of duplicate rows that’s larger than your page size … it’s
probably better just to add a rowid or some other unique integer and go back to
solution 1!)
—Jens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
