Hi All -
I'm looking for a row_number ( sql server function ) equivalent in SQLite3 or SQLitening. This would be the
row# of the output of a select statement, NOT the rowid.
Unfortunately there SQLite does not support row_number and there is no equivalent in it.
I usually use a little function with slGetRow by generating a row_number. (See example below)
FUNCTION GetRows () AS LONG
LOCAL NumFlds AS STRING
LOCAL Rec AS STRING
LOCAL i AS LONG
LOCAL row_number AS LONG
LOCAL recset AS STRING
slSel "SELECT rowid,Redref,Manuf,Product FROM Parts ORDER BY Redref LIMIT 5"
' Process records
DO WHILE slGetRow
' When a row is returned, you use the slF, slFN, slFX, slFNX functions.
Rec = FORMAT$(row_number) & " - "
FOR i = 1 TO slGetColumnCount
Rec = Rec & slF(i) & " - "
recset += rec & $CRLF
I forgot to mention, I was really looking for a solution that could be implemented in a select query only and not rely
on a traditional programming language solution.
But thanks for your comment and code. I will stop looking, for now, for a function or equivalent.
I had understood that, Skip. But unfortunately SQLite does not provide that. I had searched for that previously, but without success.
A possible solution would be to change the SQLiteningServer code to provide this functionality.
Returning the recordset into an array and selecting an element is not the solution as Rolf mentioned.
Anyway, here is an example of picking an element out of the array created using slSelAry.
FUNCTION PBMAIN ()
LOCAL sArray() AS STRING
LOCAL x AS LONG, s,sDefault AS STRING
slexe "drop table if exists MYTABLE"
slexe "create table if not exists MYTABLE (ID integer primary key autoincrement, FIRST,LAST)"
slexe "insert into MyTable values(null,'Jane' , 'Mansfield' )"
slexe "insert into MyTable values(null,'Heidi B.' , 'Klume' )"
slexe "insert into MyTable values(null,'Linda' , 'Evans' )"
slexe "insert into MyTable values(null,'Heidi A.' , 'Klume' )"
slSelAry "select * from MyTable order by LAST,FIRST", sArray(), "Q9"
FOR x = LBOUND(sArray) TO UBOUND(sArray)
s = s + sArray(x) + $CR
? s,,"Values in array"
sDefault = "1"
x = VAL(INPUTBOX$ ("Type 1 to" + STR$(UBOUND(sArray)),"Results",sDefault) )
IF x => 1 AND x <= UBOUND(sArray) THEN
? s + $CR + $CR + "You selected:" + $CR + sArray(x)
sDefault = FORMAT$(x)
LOOP WHILE X