sqlite help (rank select)

[dzek (varez)]

hi, im looking to optimal, and the fastest way to get a player rank from table that contains nick and score.

in mysql theres no problem, but sqlite dont allowing me use SET (and i cant use mysql)

my code for mysql:

SET @rank=0;
SELECT rank FROM (
      SELECT @rank:=@rank+1 AS rank
      FROM scores
      ORDER BY score DESC
) AS tmptable WHERE nick = 'player'

''any help?

[50p]

You probably want: "SELECT FROM WHERE nick='player' ORDER BY score DESC"

[dzek (varez)]

well, nope.. i need to get a "rank" of player.. i could do something like that, then loop through the result, adding to temp variable +1, until i found that record, and get a "rank", but i think this will need more resources (mean RAM etc), than creating a nice query like the one i wrote above..

im php programmer and working with MySQL everyday.. so i know what i want.. but i cannot use MySQL (i cannot install that module - nevermind why), and SQLite seems to be a .. lite version of MySQL. and it looks like i cannot set temporary variables (like my "rank") in SQLite, or i dont know how

[robhol]

Out of curiosity, why not MySQL?

If all you need to do is rank players, I see no reason why you can't just do what 50p said - something like SELECT * FROM foo ORDER BY Score DESC LIMIT bar.

If this isn't what you wanted, then you have done a crappy job of explaining it..

[dzek (varez)]

why not mysql - not my server, mysql server not installed there, and remote mysql-ing is too slow.

if i do

SELECT * FROM foo ORDER BY Score DESC LIMIT bar

i will get a score, some settings assigned to player, etc, but not the rank.. the "position" on the result..

now i can do something like that to get "rank":

local playerNameThatImLookingFor = "dzek"
local sQuery = "SELECT * FROM scores ORDER BY score DESC"
local players = executeSQLQuery(sQuery)
local rowExist = #players
if (rowExist~=nil and rowExist~=0) then
local ii=0
for key,val in ipairs(players) do
	ii=ii+1
if playerNameThatImLookingFor == val.nick then
local playerRank = ii
outputChatBox(playerRank)
end
end
end

but doing that with 1000 or more rows could be killing for the server.

with my MySQL way it will be something like:

local sQuery = "SET @rank=0; SELECT rank FROM ( SELECT @rank:=@rank+1 AS rank FROM scores ORDER BY score DESC) AS tmptable WHERE nick = 'dzek'"
local players = executeSQLQuery(sQuery)
local rowExist = #players
if (rowExist~=nil and rowExist~=0) then
for key,val in ipairs(players) do
local playerRank = val.rank
outputChatBox(playerRank)
end
end

its better to do get one row with one column, than getting 1000+ rows, then looping through the result for every player to get their rank..

[dzek (varez)]

HELL!

MTA-MySQL seems not allowing me to do two queries at once, so SET dont work too

[AdiBoy]

try something like this:

local result = executeSQLQuery( "SELECT score FROM table WHERE nick=players_nick" )
local rank = #executeSQLQuery( "SELECT score FROM table WHERE score>=" .. result[1]["score"] )

the variable 'rank' will give you his rank... this is how i use it on the skc servers and it works just fine

[dzek (varez)]

woow, nice idea dude! big thanks!