Navigate to previous, next record in SQLite db

[panoss]

I 'm using SQLite.au3.

What I want to do is navigate through the records of a recordset, go to first, last, previous, next record.

I read about indexes, I also learnt that SQLite automatically creates a column named rowid (which might be useful). And found something relative.

How would you suggest to do this? Maybe there is already some library or code snippet for this functionality?

 

 

 

Edited July 30, 2018 by panoss

[Earthshine]

the help file is a great place to start. seriously. they even have working example code

_SQLite_Query

Edited July 30, 2018 by Earthshine

[FrancescoDiMuro]

Hi @panoss

SQLite is a great tool to work with, but everything depends about on what do you want to do.

As @Earthshine stated, the best place where to start, is the Help file.

From there, you can see how the different _SQLite* functions are used, and so, you can even see if there is already something that could fit your request.

The expert about SQLite ( as I noticed in these years ), is @jchd.

So, let's see if he could suggest to you more

Edited July 30, 2018 by FrancescoDiMuro

[AutoBert]

If you want to understand SQLite, read about it: http://www.sqlitetutorial.net/ and try to make a AutoIt script. Most powerfull func in AutoIt for querying is _SQLite_GetTable2d,  for inserting, updating and dbcreation and -manipulation (Alter) you must have a look at _SQLite_Exec. 

[BrewManNH]

SQLite doesn't automatically store the rows in the order you inserted them, you'd need to index the table otherwise the rows could be in any order and you can't go forwards and backwards the way I think you're wanting to do. Better to just read the whole table into an array and navigate through it instead.

[panoss]

5 hours ago, BrewManNH said:

Better to just read the whole table into an array and navigate through it instead.

This was my first thought but doesn't sound very...'professional'.

I think the use of indexes is a better approach.

[FrancescoDiMuro]

@panoss
I don't see anything wrong with the suggest of @BrewManNH.
It's a functional, smart, and easy solution for what you're trying to do  
Just query the Database, store the result in an array, and navigate through the array.

[jchd]

13 hours ago, panoss said:

This was my first thought but doesn't sound very...'professional'.

This is how SQL works, by design. SQL is based on relational algebra and is (roughly) dealing with mathematical sets and ternary logic.

Sets have no intrinsic order so if you're asked to list elements of the set of natural integers between 1 and 5 inclusive, you're free to say "4, 2, 5, 3, 1" or "3, 4, 5, 2, 1" or "1, 2, 3, 5, 4" or whatever order you want. All these answers are correct. Of course you can define a partial or complete order over many sets and if the task specifies "... in increasing order" then the only correct answer is "1, 2, 3, 4, 5" if "increasing" means > (the greater than mathematical operator).

Moral: in SQL the query SELECT * FROM MyTable may return rows (if any) in any order. In this context, first, last, previous, next have no sense.

Many RDBMS offer windowing functions which allow to navigate ad libidum inside a resultset, but  such functions are not (yet) available in SQLite.
SQLite is like its name says: lite!

Edited July 31, 2018 by jchd
Typos

[Skysnake]

The solution may be here:

 

[jchd]

A note for SQLite users who wonder: the dev team of SQLite is in the process of offering windowing functions.

They are part of the current draft release:

http://www.sqlite.org/draft/releaselog/3_25_0.html

 

But don't forget this is just draft, subject to changes.

Edited August 3, 2018 by jchd

[vijaydeveloper]

Using PostgreSQL's, specifically and,LAGLEAD should be able to show you the previous and next entries in your table.

select *
from (
    select  id, thread_id, is_notice, title, content,
            lag(id) over (order by is_notice desc, thread_id desc, id asc) as prev,
            lead(id) over (order by is_notice desc, thread_id desc, id asc) as next
    from post
    ) x
where 3 IN (id, prev, next);

Too learn more or get SQLite tutorials from here.

[jchd]

The definitive source of SQLite docs is https://www.sqlite.org/index.html