[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

[James K. Lowden]

On Thu, 3 Mar 2016 10:43:26 +0800 (CST)
?? <qiulang2000 at 126.com> wrote:

> > Can anyone describe a situation for which this style of LIMIT &
> > OFFSET is advisable from the application's point of view?  (The
> > DBMS costs are obvious enough.)  
> 
> For me this is the easiest way to implement a scrolling cursor.
> Otherwise I will have to record for each table the condition I use
> to  scroll it. So if there is an easier way to implement the
> scrolling cursor please let me know.

You say, "record ... the condition ... to  scroll [the table]".  I'm
sure I don't know what "condition" you mean.  

You appear to be doing something like:

        offset = 0
        do 
                sqlte3_prepare (limit, offset)
                do
                        sqlite3_step
                until SQLITE_DONE
                offset += limit
                ... application stuff ...
        until last row processed

The obvious, better alternative is

        sqlte3_prepare
        do 
                do
                        sqlite3_step
                until LIMIT
                ... application stuff ...
        until last row processed

because in the second example you're using SQLite's cursor to "record
the condition of the table".  There's nothing for you to keep between
"pages"; you just start from where you stopped.  

How exactly is the first way "easiest"?  

--jkl