I need a simple scrolling function for my sqlite table, returning the previous X records or the next X records from the last time I selected. As the rule #2 "Do not try to implement a scrolling window using LIMIT and OFFSET" said here, I change to sql from
SELECT * FROM tracks WHERE xxx LIMIT 5 OFFSET :index
to
SELECT * FROM tracks WHERE xxx and rowid > :index LIMIT 5
I do some simple test and the result seems to the same, eg. select rowid from tracks where rowid > 100 limit 5 and select rowid from tracks limit 5 offset 100 both return the same 5 records after rowid 100.
So my question is <1> is the value specified in OFFSET the same as rowid (assume I did not delete record)? <2> Did my change better ？
Update, I know when delete record, rowid may change. So my question is assuming I did not delete record, is the value used in OFFSET the same as rowid ? If not, what exactly does that value mean? And if I do delete some record, what exactly does that value means ?
Also, the article there said "This information is obsolete" but I just can't find the updated information!
When you have deleted some rows, the rowid number are not longer the same as the index.
To show the rows for the next page, you should not use an index value, but the last value of the column that you're sorting by from the last page.