Jump to content

Sqlite "SELECT row_number() Over() as 'Pos.' FROM TABLE"


Recommended Posts

I am running into a situation that i currently can't figure out an alternative solution for in AutoIT3:
 

$DBHandle = OpenDb($DbaseFile)
Local $Query = "SELECT row_number() Over() as 'Pos.',* " & _
        "FROM TABLE1,TABLE2 " & _
        "where TABLE1.ID = TABLE2.ID  " & _
        "GROUP BY TABLE1.NAME;"

Local $Queryresult = Query2D($DBHandle, $Query)
;_ArrayDisplay($Queryresult)


Func Query2D($DBHandle, $DBCommand)
    Local $iRows, $iColumns, $QueryResult
    If IsArray($QueryList) Then
        ReDim $QueryList[1][1]
    Else
        Dim $QueryList[1][1]
    EndIf

    $QueryList[0][0] = 0

    $QueryResult = _SQLite_GetTable2d($DBHandle, $DBCommand, $QueryList, $iRows, $iColumns)

    If $QueryResult = $SQLITE_OK Then
        Return $QueryList
    Else
        Return -1
    EndIf
EndFunc   ;==>Query2D

I get a syntax error
--> Function: _SQLite_Query
--> Query:    SELECT row_number() Over() as 'Pos.' FROM TABLE1,TABLE2 where TABLE1.ID=TABLE2.ID GROUP BY TABLE1.NAME;
--> Error:    near "(": syntax error
Using the actual Sqlite dll's you can download from their site.

The query works without issues in querytools like Sqlitestudio.
When i diminish the query down to try it with just "row_number()", it calls row_number an undefined column.
When trying to replace it with rowid, oid or _rowid_, the undefined column error remains.

I am trying to get the rowid as first column with a custom title, but if i can't even use the original rowid definitions, i am out of options.

I could resort to manually add a column into the array and populate it with a simple row ID, but it would be nice if the more complex row_number() function and partitioning would be working...
https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

 

Link to post
Share on other sites

Sorry, i solved it myself -> I had a fileinstall line copying an older version of the library (03.22.0) over the latest one (03.40.00)
Would have been smart to copy the latest edition to the path where the fileinstall was gaining these documents from.
(It helps to consolewrite the actual library version loaded to see you are running the latest)

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...