Indexes in SQLite

[johnorourke1351]

Hi

I have tried to create a reverse key for my main key in a simple database so that I can read the key in reverse order (to do a read previous on the table).

I use a query like select * from xyz table indexed by revindex. Revindex contains the key from the xyz table but in descending order.

The query says there is no solution for the query unless I use the key in the following query:
select mainkey from xyz table indexed by revindex. In this case the data is displayed but it appears in mainkey order not reverse main key order of the revindex.

1
2
3
4
5
Is what appears in the output to the query instead of 5 4 3 2 1.
Is there any way to get sqlite to do this the way i want it to work so I can read previous?

John O'Rourke

[infratec]

Hi,

use ORDER BY ... DESC
or ORDER BY ... ASC

This inverts the order.

Bernd

[johnorourke1351]

Hi
That is what I did. But I was hoping i could use the reverse index to do an efficient lookup in reverse

Here is what I do now :
SQL$ = "SELECT * FROM JobData WHERE JobID < '" + LASTKEY$ + "' order by JobID DESC LIMIT 1 ;"
This seems pretty inefficient to go through all the data just to get to one greater than the last record read.

Thanks

John O'Rourke

[infratec]

Hi,

if JobID is an integer why you don't select the value -1 direct?

SQL$ = "SELECT * FROM JobData WHERE JobID = " + str(val(LASTKEY$) - 1)

Bernd

[johnorourke1351]

Yeah that could work if there arn't too many gaps. If there are then you would keep subtracting until you found something.