PureBasic Forums - English

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

Hi,

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

This inverts the order.

Bernd

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

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

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