[iceeedr]

Good morning, I have an unsolved problem when making a query with SQLite, in the DB Browser for SQLite program the query returns normally with all the information, but when trying to perform the query in the game I get the following error:

Error: near "(": syntax error

The querie

PHP Code:

SELECT ROW_NUMBER() OVER (ORDER BY 1.0 * kills / deaths DESC) Pos, kills, deaths, auth FROM tablename WHERE enabled = 1 AND kills > 0 AND deaths > 0 AND kills > 100; 


PHP Code:


#define SQL_QUERY_CATCH_UPDATED_RANK    "SELECT ROW_NUMBER() OVER (ORDER BY 1.0 * kills / deaths DESC) Pos, kills, deaths, auth FROM %s WHERE enabled = 1 AND kills > 0 AND deaths > 0 AND kills > 100;"

public UpdatedRank(id)
{
        new szQuery[180]
        formatex
        (
                szQuery,
                charsmax(szQuery),
                SQL_QUERY_CATCH_UPDATED_RANK,
                SQL_TABLE
        )

        new szData[2]
        szData[0] = QUERY_UPDATE_RANK
        szData[1] = id

        SQL_ThreadQuery(g_hTuple,"SQL_HandleGlobal",szQuery,szData,sizeof(szData))
        return PLUGIN_HANDLED
}

public SQL_HandleGlobal(iState,Handle:hResult,szError[],iError,szData[],iSize)
{
        if(iState == TQUERY_SUCCESS)
        {
                switch(szData[0])
                {
                        case QUERY_UPDATE_RANK:
                        {
                                if(is_user_connected(szData[1]))
                                {
                                        if(SQL_NumRows(hResult))
                                        {
                                                new szSteamid[MAX_AUTHID_LENGTH]
                                                new iPos
                                                while(SQL_MoreResults(hResult))
                                                {
                                                        iPos = SQL_ReadResult(hResult, 0)
                                                        SQL_ReadResult(hResult, 3, szSteamid, charsmax(szSteamid))

                                                        if(equal(szSteamid, userData[szData[1]][szAuth]))
                                                        {
                                                                userData[szData[1]][Rank] = iPos
                                                        }

                                                        SQL_NextRow(hResult)
                                                }
                                        }
                                }
                        }
                }
        }
        else
        {
                log_amx("Error: %s", szError)
        }
} 


[Shadows Adi]

PHP Code:

SELECT ROW_NUMBER() OVER (ORDER BY 1.0 * kills / deaths DESC), Pos, kills, deaths, auth FROM tablename WHERE enabled = 1 AND kills > 0 AND deaths > 0 AND kills > 100; 


You forgot a comma after

PHP Code:

deaths DESC) 


[iceeedr]

Originally Posted by Shadows Adi PHP Code: SELECT ROW_NUMBER() OVER (ORDER BY 1.0 * kills / deaths DESC), Pos, kills, deaths, auth FROM tablename WHERE enabled = 1 AND kills > 0 AND deaths > 0 AND kills > 100;  You forgot a comma after PHP Code: deaths DESC)

I believe that is not the problem, even because when adding the comma (which does not make sense in the syntax) the querie does not even return in the program.

Edit 1:

Error code is 1..

(1) SQLITE_ERROR

The SQLITE_ERROR result code is a generic error code that is used when no other more specific error code is available.

[JocAnis]

Did you try same query with MySql? How i remember some functions are not possible to query with sqlite, like Auto_increment, so maybe thats the issue

[iceeedr]

Originally Posted by JocAnis Did you try same query with MySql? How i remember some functions are not possible to query with sqlite, like Auto_increment, so maybe thats the issue

Yes, as you said Auto_increment is not possible in sqlite, however, AUTOINCREMENT yes, as I demonstrated in the sqlite db browser program the syntax is correct, I am thinking that the sqlite version of amxx may not be as current and does not support "ROW_NUMBER () OVER ()"

Which leads me to think about how to count the querie lines by returning them in a column, what a boring situation...

[Bugsy]

What are you trying to do? Maybe it can be written a different way

Edit: Try this, you will need to add the few other conditions that you have.

PHP Code:

SELECT 
  ( SELECT COUNT(*) + 1 
    FROM tblData
    WHERE ((Kills/Deaths) > (t.Kills/t.Deaths) OR ((Kills/Deaths) = (t.Kills/t.Deaths) AND id<t.id))
  ) as rowIndex, 
  t.Kills/Deaths,
  AuthID
FROM tblData t 
ORDER BY rowIndex ASC; 


Source [ID, kills, deatrhs, authID]

"1"	"20"	"9"	"STEAM_1"
"2"	"50"	"12"	"STEAM_2"
"3"	"90"	"33"	"STEAM_3"
"4"	"52"	"9"	"STEAM_4"
"5"	"31"	"55"	"STEAM_5"

Result [Rank, Kills/Deaths, AuthID]

"1"	"5"	"STEAM_4"
"2"	"4"	"STEAM_2"
"3"	"2"	"STEAM_1"
"4"	"2"	"STEAM_3"
"5"	"0"	"STEAM_5"

[iceeedr]

Originally Posted by Bugsy What are you trying to do? Maybe it can be written a different way Edit: Try this, you will need to add the few other conditions that you have. PHP Code: SELECT    ( SELECT COUNT(*) + 1      FROM tblData     WHERE ((Kills/Deaths) > (t.Kills/t.Deaths) OR ((Kills/Deaths) = (t.Kills/t.Deaths) AND id<t.id))   ) as rowIndex,    t.Kills/Deaths,   AuthID FROM tblData t  ORDER BY rowIndex ASC;  Source [ID, kills, deatrhs, authID] "1" "20" "9" "STEAM_1" "2" "50" "12" "STEAM_2" "3" "90" "33" "STEAM_3" "4" "52" "9" "STEAM_4" "5" "31" "55" "STEAM_5" Result [Rank, Kills/Deaths, AuthID] "1" "5" "STEAM_4" "2" "4" "STEAM_2" "3" "2" "STEAM_1" "4" "2" "STEAM_3" "5" "0" "STEAM_5"

What I want is not difficult, I just want to take the line number in the querie to determine the "rank" of the player, the problem with your suggestion is that it is extremely expensive, while my querie performs in 80 ~ 90ms its delay 1980 ms on average.

Could you give me your syntax analysis below? It would have to change the way the querie is made, since the search would be straight from the steamid table, but it was supposed to work, what do you think?

PHP Code:

SELECT (COUNT(*) + 1) AS Rank FROM tablename WHERE ((1.0 * kills) / deaths) > (SELECT ((1.0 * kills) / deaths) FROM tablename WHERE auth = '%s' AND enabled = 1 AND kills > 0 AND deaths > 0 AND kills > 100 AND kills > deaths); 


[Bugsy]

Not sure I can help then.

Using the above, this will get you an individual players rank

PHP Code:

SELECT Rank
FROM (SELECT 
  ( SELECT COUNT(*) + 1 
    FROM tblData
    WHERE ((Kills/Deaths) > (t.Kills/t.Deaths) OR ((Kills/Deaths) = (t.Kills/t.Deaths) AND id<t.id))
  ) as Rank, 
  t.Kills/Deaths,
  AuthID
FROM tblData t 
ORDER BY Rank ASC)
WHERE AuthID="STEAM_3"; 


[iceeedr]

Solved with some small changes, which in this case are the filters, in the query proposed by Bugsy, thanks for the time spent.