How do I get ranking of a specific player from a MySQL table. Here's the table's schema: Code: +--------+---------+ | Player | Balance | +--------+---------+ | Edeire | 120044 | | Tajfhg | 385848 | | Hafjw | 38288 | +--------+---------+ I know this would arrange the data in ascending order by "Balance" PHP: /** @var \mysqli $db */$db->query("SELECT Player, Balance FROM table ORDER BY Balance"); What I want is the ranking of "Edeire" based on all the balances. An alternative to a while loop like the one below. PHP: /** @var \mysqli $db */$query = $db->query("SELECT Player, Balance FROM table ORDER BY Balance");$rank = 0;while($res = $query->fetch_array()){ ++$rank; if($res["Name"] == "Edeire"){ break; }}if($rank !== 0){ echo "Edeire is ranked $rank based on their economic status.";}
PHP: /** @var \mysqli $db */$query = $db->query("SELECT Player, Balance FROM table ORDER BY Balance DESC");$rank = 0;$player = array();while($res = $query->fetch_array()){ ++$rank; $player[$res['Name']] = $rank;}if($rank !== 0){ echo "Edeire is ranked ".$player['Edeire']." based on their economic status.";}
PHP: SELECT COUNT(Player)+1 AS rank FROM table WHERE Balance > (SELECT Balance FROM table WHERE Player='Edeire'); Code: +------+ | rank | +------+ | 2 | +------+ 1 row in set (0.00 sec)
I'm not sure about the exact performance, but a JOIN query might be better than doing a new SELECT every time: http://sqlfiddle.com/#!9/f48b5/5 (if it doesn't work, see the spoiler below) Note that I used < instead of <= in the ON clause, and had a COUNT() +1 when defining the rank column. This is because if I use <=, duplicates will also be counted and we will have "1 2 4 4 5 6" rather than "1 2 3 3 5 6" in the result. Also note that HAVING rather than WHERE is used. Spoiler Code: CREATE TABLE tbl ( name VARCHAR(20) PRIMARY KEY, bal INT ); INSERT INTO tbl (name, bal) VALUES ('alice', 5), ('bob', 3), ('carol', 6), ('dan', 9), ('erin', 4), ('frank', 5); Code: SELECT * FROM tbl ORDER BY bal DESC; SELECT t1.name, COUNT(t2.name) + 1 AS rank FROM tbl t1 LEFT JOIN tbl t2 ON t1.bal < t2.bal GROUP BY t1.name ORDER BY rank; SELECT t1.name, COUNT(t2.name) + 1 AS rank FROM tbl t1 LEFT JOIN tbl t2 ON t1.bal < t2.bal GROUP BY t1.name HAVING t1.name = 'alice'
Did you even test how long it takes with the while loop? @Muqsit? Spoiler with #2: 0.014381885528564 with #3: 0.0037500858306885 with #4: 0.6971070766449
It was an exaggerated joke Btw thanks @SOFe. I guess"HAVING" isn't commonly used or something, I didn't know it existed.