Top 5 PVP SQL Query

[simplynice]

So i have this global_reg_val:

PVPTotal

so everytime someone killed someone it adds : PVPTotal = 1 + 1 etc..

Now how can i query this in sql as top 5 highest?

I can't make it work though:

How to get the name of the account_id

//set @nb, query_sql("select account_id,name,str,value from `global_reg_value` WHERE str = 'PVPTotal' ORDER BY str DESC LIMIT 5", @name$, @value,@str ,@account_id);
//mes "Hall Of Fame: TOP5";
//mes "1.("+@value[0]+")"; // Will return a person with the biggest fame value.
//mes "2.("+@value[1]+")";
//mes "3.("+@value[2]+")";
//mes "4.("+@value[3]+")";
//mes "5.("+@value[4]+")";
//close;

Edited February 13, 2012 by simplynice

[Brian]

Here's one way you could do the SQL query:

SELECT `char`.`name`, global_reg_value.`value` 
FROM global_reg_value LEFT JOIN `char` ON global_reg_value.char_id=`char`.char_id 
WHERE global_reg_value.`str` = 'PVPTotal' 
ORDER BY CAST(global_reg_value.`value` AS SIGNED) DESC 
LIMIT 5;

So your NPC script could look something like:

query_sql "SELECT `char`.`name`, global_reg_value.`value` " + 
         "FROM global_reg_value LEFT JOIN `char` ON global_reg_value.char_id=`char`.char_id " + 
         "WHERE global_reg_value.`str` = 'PVPTotal' " + 
         "ORDER BY CAST(global_reg_value.`value` AS SIGNED) DESC " + 
         "LIMIT 5", .@name$, .@points;

mes "Hall Of Fame: TOP5";
for (set .@i,0; .@i < getarraysize(.@name$); set .@i,.@i+1) {
mes (.@i+1) + ". " + .@name$[.@i] + " ("+ .@points[.@i] +")";
}

[simplynice]

Thank you very much sir.