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.
Any thoughts?
Skip
Unfortunately there SQLite does not support row_number and there is no equivalent in it.
http://www2.sqlite.org/cvstrac/wiki?p=UnsupportedSqlAnalyticalFunctions
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
slopen "sample.db3"
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.
INCR row_number
Rec = FORMAT$(row_number) & " - "
FOR i = 1 TO slGetColumnCount
Rec = Rec & slF(i) & " - "
NEXT
recset += rec & $CRLF
LOOP
MSGBOX recset
END FUNCTION
Rolf
Rolf -
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.
Skip
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.
Rolf
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.
#DIM ALL
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN ()
LOCAL sArray() AS STRING
LOCAL x AS LONG, s,sDefault AS STRING
slOpen "junk.db3","C"
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
NEXT
? s,,"Values in array"
sDefault = "1"
DO
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)
END IF
LOOP WHILE X
END FUNCTION