SQLitening Support Forum

The attached zip file seems to be corrupt, can someone please mail me a good one. Thx

Hello Jacob,

I reuploaded the zip file. Should work now.

Rolf

Thanks.

If there are duplicates in a column wouldn't the previous and next always get the same record?

IF TRIM$(SearchTxt) = "" THEN
    SELECT CASE Direction
      CASE "<<"
        t1 = ""
        t2 = " LIMIT 1"
      CASE "<"
        T1 = " AND " & SrtFld & " " & direction & " '" & RecVal & "'"
        t2 = " DESC LIMIT 1"
      CASE ">"
        t1 = " AND " & SrtFld & " " & direction & " '" & RecVal & "'"
        t2 = " LIMIT 1"
      CASE ">>"
        t1 = ""
        t2 = " DESC LIMIT 1"
      CASE ELSE

    END SELECT
    t = "SELECT " & Flds & " FROM " & Tbl & " WHERE "   & WhereStr & _
      t1 & " ORDER BY " & SrtFld & t2

  ELSE
    t = "SELECT " & Flds & " FROM " & Tbl & " WHERE "
    IF TRIM$(WhereStr) = "" THEN
      t = t & SrtFld & " LIKE '%" & SearchTxt & "%' LIMIT 1"
    ELSE
      t = t & WhereStr &" AND " & SrtFld & " LIKE '%" & SearchTxt & "%' LIMIT 1"
    END IF
END IF                 

CJ - you are perfectly correct! Have to make some changes to it.

rb

Not sure if SQLite added cursors.  See some examples using them on the net.
This thread is about not using them and using slSelAry instead.
http://www.sqlitening.com/support/index.php?topic=2636.0 


--FIRST and LAST is easy, but previous and next?
--column + rowid search needs to be worked out for previous and next?

create table if not exists t1(f1 integer primary key autoincrement, f2);
create index if not exists index_name on t1 (f2);

-- Insert 'A' 3-times into column f2
insert into t1 (f1,f2) values (null,'A'),(null,'A'),(null,'A');

-- (get last) order by f2,f1 so descending if no index;
select 'LAST ' ,f2,rowid from t1 order by f2,f1 desc LIMIT 1;

-- (get first);
select 'FIRST' ,f2,rowid from t1 order by f2 ASC LIMIT 1;   

results:
LAST   A  3 
--------------------------------------------------
FIRST  A  1