Need to perform ORDER BY twice, problems with ranking

Question

I have been struggling to set this up for months and months! I need help with setting rank to my database.

This is how my current code looks like:

$db->queryNoReturn("SET @a:=0");
return $db->query("
    SELECT * FROM
        (SELECT 
                `FFA_Stats`.`id`, 
                `FFA_Stats`.`player_uuid`,
                `FFA_Stats`.`points`,
                `FFA_Stats`.`hits`,
                `FFA_Stats`.`shots`,
                `FFA_Stats`.`wins`,
                `FFA_Stats`.`tkills`, 
                `FFA_Stats`.`tdeaths`, 
                (`FFA_Stats`.`tkills`/`FFA_Stats`.`tdeaths`) as `KDR`, 
                `player`.`name`, 
                `player`.`uuid`, 
                `player`.`online`, 
                (@a:=@a+1) AS rank
            FROM `FFA_Stats`
            INNER JOIN `player` ON `FFA_Stats`.`player_uuid`=`player`.`uuid`
            ORDER BY `points` DESC
        ) AS `sub`
    ");

Basically its sorting it by points and you can check how it looks like here: http://filipvlaisavljevic.com/clash/ffa.php

All I want to do is add rank to the sorted table so the player with the most points would be #1 etc.

Does anyone know what to do?

I would just do it in a PHP output loop, but this sounds like: http://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table — manassehkatz-Moving 2 Codidact, Feb 20 '17 at 00:30
How can I do it in a PHP output loop? This is how my php file looks like: http://i.imgur.com/8oBvide.png — , Feb 20 '17 at 09:14

SH_ · Answer 1 · 2017-02-19T21:21:48.553

0

Usually a rank number would be an integer that you could generate from iterating through the rows of the query result. eg. echo $count++;

If you have calculated or attributed a rank in your database then you can add 'order by' statements separated by commas. eg.

    FROM `FFA_Stats`
    INNER JOIN `player`
    ON `FFA_Stats`.`player_uuid`=`player`.`uuid`
    ORDER BY `rank` DESC, `points` DESC) AS `sub`
        ");

edited Feb 19 '17 at 21:21

answered Feb 19 '17 at 21:20

SH_

266
2
3
17

I think I have in the code above... ie. ORDER BY `rank` DESC, `points` DESC). – SH_ Feb 19 '17 at 21:23
The problem is that its assigning rank before sorting the table, then when I sort it by rank, it is not the correct order. – Feb 20 '17 at 09:11

Need to perform ORDER BY twice, problems with ranking

1 Answers1