SQL Query to show a Player Rank Position

[raksone 0]

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 0]

Hello guys,

I could not figure out how to do it.

Anyone does know how to?

Thanks...

[CarlosHenrq 13]

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. 

Edited September 3, 2018 by CarlosHenrq

[AnnieRuru 958]

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;