SQLite 3 Row_number equivalent?

[Skip]

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

[Rolf Brandt]

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)

Code Select Expand


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

[Skip]

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

[Rolf Brandt]

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

[cj]

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