Ordering users by "rank counts" column with or without ORDER BY in MySQL

Question

I have a game and it have 10 worlds and each world have 20 levels.

and have a ranks table like this:

id	user_id	world_num	level_num	rank
#	userId1	1	1	3
#	userId1	1	2	1
#	userId1	5	15	12
#	userId1	2	5	2
#	userId2	1	1	1
#	userId2	1	2	2
#	userId2	5	15	4
#	userId2	2	5	1
...

so user_id,world_num,level_num group is uniq (each user only have one "world_num = 1,level_num = 1" row and rank)

and i have a rank column it shows the rank for world_num,level_num group. (For every levels i ranked the list with code.)

I want to show a "Overall Ranking list screen", its will show Global Top rank users.

I want to write a sql code for:

The user with the most "rank 1" should be top of the list, if two users have same "rank 1" count than the user with the most "rank 2" should be the second and so on...

I write a sql code like this:

SELECT `user_id`, `rank`, COUNT(`rank`) AS 'rank_count'
FROM score
GROUP BY `rank`

when i run this i have ranks and rank counts but i couldn't order like:

ORDER BY 'rank_count_1', 'rank_count_2', 'rank_count_3', 'rank_count_4' ....

A table i want to show is like that:

1. userId92 (rank1Count = 60)
2. userId45 (rank1Count = 57, rank2Count = 46, rank5Count = 8)
3. userId12 (rank1Count = 57, rank2Count = 40, rank3Count = 17)
4. userId76 (rank1Count = 55, rank3Count = 20, rank4Count = 80)
5. userId33 (rank1Count = 55, rank4Count = 25, rank5Count = 35)
...

How can i write a sql for "Overall Ranking list"?

column user_id here is exactly what i want: https://dbfiddle.uk/-YLilhd4

Answer 1

For this, you can use dynamic sql to achieve that. Here is the sample code

SET @sql = CONCAT('
    SELECT user_id, ', (
        SELECT GROUP_CONCAT(
            CONCAT(
                'MAX(CASE WHEN user_rank = ', user_rank,
                ' THEN rankCount ELSE 0 END) AS rank', user_rank, 'Count'
            )
            ORDER BY user_rank
        )
        FROM (
            SELECT DISTINCT user_rank FROM score
        ) AS subquery
    ), '
    FROM (
        SELECT user_id, user_rank, COUNT(*) AS rankCount
        FROM score
        GROUP BY user_id, user_rank
    ) AS counts
    GROUP BY user_id
    ORDER BY ', (
        SELECT GROUP_CONCAT(CONCAT('rank', user_rank, 'Count DESC') ORDER BY user_rank)
        FROM (
            SELECT DISTINCT user_rank FROM score
        ) AS subquery
    )
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is the sample dbfiddle to test it out. But, it may just rank based on available rank. As you can see, it will be rank 1,2,3,4,12 and no rank 5,6,...,11 because can't found in sample data.