CODEWITHSUNDEEP
sqlitequery-optimizationcursor-position
Qiulang
Qiulang

Reputation: 12445

Is the value specified in OFFSET the same as rowid (sqlite)？

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!

Upvotes: 0

Views: 338

Answers (1)

CL.
CL.

Reputation: 180182

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.

Upvotes: 1

Related Questions