• Welcome to SQLitening Support Forum. Please login.
 
December 03, 2021, 07:20:57 PM

News:

Welcome to the SQLitening support forums!


SQLite 3 Row_number equivalent?

Started by Skip, October 08, 2013, 01:15:48 AM

Previous topic - Next topic

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

October 08, 2013, 06:35:48 AM #1 Last Edit: October 08, 2013, 06:55:15 AM by 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)


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
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

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
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

cj

March 18, 2014, 06:05:32 PM #4 Last Edit: March 18, 2014, 06:15:01 PM by 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