Easy way to scroll through table

(1) By Scott Doctor (scottdoctor) on 2021-11-29 08:57:26 [source]

Given a table with a bunch of rows in it. I am writing a windows program for displaying a row's data on a GUI form. I am trying to figure out a simple way to know the row-id of the next/previous row in the table when sorted by a Julian date column. The idea is to have a simple NEXT and PREVIOUS button on the screen to scroll through the rows showing the current row's data in the form window. Although the primary key is unique, rows were deleted and inserted after editing so the row-id when sorted on the Julian date column are not necessarily sequential, and gaps in the sequence exist due to deleting some rows.

Is there a SQLite function or SQL command to determine the next/previous rowid when sorted on a column?

I am thinking doing a SELECT with a SORT and populate a temporary table with the row-id in the order they come out. However the database has several thousand rows and this seems an inefficient way of doing it.

(2) By anonymous on 2021-11-29 10:30:09 in reply to 1 [link] [source]

Finding the first row:

    select * from things
        order by date, rowid
        limit 1;

Finding the last row:

    select * from things
        order by date desc, rowid desc
        limit 1;

Finding the next row with the current date and rowid as bound parameters:

    select * from things
        where (date, rowid) > (?1, ?2)
        order by date, rowid
        limit 1;

Finding the previous row with the current date and rowid as bound parameters:

    select * from things
        where (date, rowid) < (?1, ?2)
        order by date desc, rowid desc
        limit 1;

This is for working with one row at a time. For a whole page at a time, raise the limit as appropriate.