SQL Query to show a Player Rank Position

[raksone]

Hello community,

I've been trying to create or research a sql query to return a Player's Top Position Number through a command @LRank.

I could have them listed by positions but I really do not know how to return the exact TOP # for the player when using the command even if he is not within the Top 10 Ranking.

I was wondering if you could assist me by finding out the correct script to do it.

Below is my script so far which is properly working at the moment:

OnRank:
dispbottom " ";
dispbottom "=============== Ranking ===============", 0xffe066;
soundeffect "hp.wav", 0; // this will play the soundeffect
dispbottom " ", 0xFA4040;
dispbottom " Top 10 List", 0xFA4040;
dispbottom " ", 0xFA4040;
set .@size, query_sql( "select name, points from mvp_ranking where points > 0 order by points desc limit "+ .top, .@name$, .@points );
for (set .@c, 0; .@c < .@size; set .@c, .@c + 1)
dispbottom "[ " +(.@c + 1) +" ] : " + .@name$[.@c] +" : [ " + .@points[.@c] +" ] Pts", 0xffffff;
dispbottom " ", 0xFA4040;

*********** THIS IS THE PART THAT I'M STRUGGLING WITH ***********

dispbottom "============== My Rank ==============", 0xffe066;
query_sql "SELECT `char_id` , `points` FROM `mvp_ranking` ORDER BY `points` DESC",.@char_id,.@rank;
dispbottom "My Top is: " + .@rank+ ".", 0x6666ff;
dispbottom "==========================================", 0xffe066;
end;


I hope you can give me a hand on this.

Thank you.

 

[raksone]

Hello guys,

I could not figure out how to do it.

Anyone does know how to?

Thanks...

 

[CarlosHenrq]

First,

If you don't keep the ranking in order, you need get the global ranking before anything.

My first suggest:

-> Create a NPC timer that updates your "global ranking" every 5min, cache it in memory.
-> When player type the cmd, he'll see the cached rank for him.

My second suggest:

-> Create a NPC that reads the "global ranking" and update it manually in the memory, this way, the cache'll be always updated... not need 5min to update.
-> When player type the cmd, he'll see the updated rank for him.

My last suggest:

-> Create a view ordered by you ranking creteria (kills, deaths, points) and put it to show the row... then run select into the view, after it, search char_id and show the exacly position. 

 

[AnnieRuru]

the answer is exactly on the no.6 of the pinned topic

@raksone

dispbottom "============== My Rank ==============", 0xffe066;
	query_sql "SELECT `points`, 1+(SELECT COUNT(1) FROM `mvp_ranking` t1 WHERE t1.`points` > t2.`points`) FROM `mvp_ranking` t2 WHERE `char_id` = "+ getcharid(0), .@points, .@rank;
	dispbottom "My MVP Points "+ .@points + ".", 0x6666ff;
	dispbottom "My Rank is: " + .@rank + ".", 0x6666ff;
	dispbottom "==========================================", 0xffe066;
	end;