SQL Command for this..

[Yuka]

This probably belongs into Third Party Support, I've read the read note too late, sorry. :/

 

So for now I have this:

query_sql "SELECT `char`.char_id,`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='PvPKDA1' ORDER BY CAST(`global_reg_value`.`value` AS UNSIGNED) DESC LIMIT 50", .@char_idpvp, .@namepvp$, .@countpvp;

To get my PvP Ladder sorted by PvPKDA1, now every player also has an entry called PvPKills.

And I want to get the Top 50 ordered by PvPKDA1, but only those who have over 50 Kills.

 

So something like

WHERE global_reg_value.str='PvPKDA1' and PvPKills > 50

I guess it's something like:

WHERE global_reg_value.str='PvPKDA1' and (SELECT global_reg_value.`value` FROM global_reg_value WHERE global_reg_value.str='PvPKills') > 50

But I somehow need to add that it should just select the one I am referring at the moment.. D:

Edited April 7, 2014 by Greyford

[Cydh]

SELECT `c`.`name`, `g`.`value` FROM `global_reg_value` AS g JOIN `char` AS c ON `g`.`char_id` = `c`.`char_id` AND `g`.`str` = 'PvPKDA1' AND `g`.`value` > 50 ORDER BY `g`.`value` DESC LIMIT 50

[Yuka]

SELECT `c`.`name`, `g`.`value` FROM `global_reg_value` AS g JOIN `char` AS c ON `g`.`char_id` = `c`.`char_id` AND `g`.`str` = 'PvPKDA1' AND `g`.`value` > 50 ORDER BY `g`.`value` DESC LIMIT 50

That only gives me all players with a PvPKDA1 over 50, but I want all with PvPKillsNew over 50 and then descending in their PvPKDA1 order. D:

Anyway, thanks for that for now, I think it is pretty close.

 

Example:

Player1 50-3

Player2 20-1

Player3 100-70

Player4 56-50

 

Then the order will be:

Player1

Player3

Player4

 

And Player2 won't appear since he has <50 kills (even though has the best KDR!)

Edited April 7, 2014 by Greyford

[Cydh]

SELECT `c`.`name`, var1 = `g`.`value`, var2 = (
    SELECT `g2`.`value` 
    FROM `global_reg_value` AS g2 
    WHERE `g2`.`str` = 'PvPKillsNew' 
    AND `g2`.`char_id` = `c`.`char_id`
    ) 
FROM `global_reg_value` AS g 
JOIN `char` AS c 
ON `g`.`char_id` = `c`.`char_id` 
WHERE `g`.`str` = 'PvPKDA1'
AND `var2` > 50 
ORDER BY `var1`
DESC LIMIT 50

[Yuka]

SELECT `c`.`name`, var1 = `g`.`value`, var2 = (
    SELECT `g2`.`value` 
    FROM `global_reg_value` AS g2 
    WHERE `g2`.`str` = 'PvPKillsNew' 
    AND `g2`.`char_id` = `c`.`char_id`
    ) 
FROM `global_reg_value` AS g 
JOIN `char` AS c 
ON `g`.`char_id` = `c`.`char_id` 
WHERE `g`.`str` = 'PvPKDA1'
AND `var2` > 50 
ORDER BY `var1`
DESC LIMIT 50

#1054 - Unknown column 'var1' in 'field list' 

Sorry, I'm quite not familiar with SQL syntax.

Can you really declare variables with just "x = .."?

[Yuka]

Bump.

[Patskie]

Use having instead : 

SELECT `char`.`name`, `global_reg_value`.`value` FROM `char` 
JOIN `global_reg_value` ON `char`.`char_id` = `global_reg_value`.`char_id` 
WHERE `global_reg_value`.`str` = 'YOUR VARIABLE'
HAVING `global_reg_value`.`value` > 5
ORDER BY CAST( `global_reg_value`.`value` AS UNSIGNED ) DESC LIMIT 50;

Don't forget to change YOUR VARIABLE on the query

[Yuka]

 

Use having instead : 

SELECT `char`.`name`, `global_reg_value`.`value` FROM `char` 
JOIN `global_reg_value` ON `char`.`char_id` = `global_reg_value`.`char_id` 
WHERE `global_reg_value`.`str` = 'YOUR VARIABLE'
HAVING `global_reg_value`.`value` > 5
ORDER BY CAST( `global_reg_value`.`value` AS UNSIGNED ) DESC LIMIT 50;

Don't forget to change YOUR VARIABLE on the query

 

Well, the problem is the "YOUR VARIABLE" is actually two variables.

I want to order the data by PvPKDA, but I only want to pick char_ids that have more than 49 kills.

 

So my HAVING needs to look at the variable PvPKills of player X and my ORDER BY CAST needs to look at the variable PvPKDA of player X.

[Yuka]

Bump.

Bump.

[Yuka]

Never mind, I have taught myself some basic SQL and figured it out.

Edited June 13, 2014 by Yuka