MySQL - Return list of top 5 users, while including logged in user

I'm trying to return the users with the top 5 highest scores in a database and assign each user a rank, which I can then display in a high score table. I want the high-score table to contain a maximum of 5 rows, but I also want it to show the rank of the logged-in user amongst the other players. Let's say the logged-in user is 'jennie'.

SELECT *
    FROM (
        SELECT *, @i := @i+1 i,
        (SELECT COUNT(DISTINCT highscore) FROM users s
        WHERE s.highscore>=r.highscore) AS rank
        FROM users r JOIN (SELECT @i:=0) vars
        ORDER BY highscore DESC
    ) x
WHERE name = 'jennie'
    OR i <= 5
        ORDER BY rank ASC;

(Using MySQL 5.0.96). Here is a fiddle showing off the query. My issue is the query operates differently based on one condition:

1: If jennie is in the top 5 results, 5 rows will be returned, containing jennie amongst the other users.

2: If jennie is not in the top 5 results, 6 rows will be returned, where the last row contains jennie.

How can I use somehow truncate the list of top users in case 2, from 5 users down to 4 users, so when jennie appears on the end she makes up the 5th row? Thanks for your help.

1 answer

  • answered 2019-08-19 03:32 Nick

    You can achieve your desired result by doing some clever ordering. First we order by whether rank is <= 4, then by whether the user is 'jennie' and her rank is > 5, and then by rank. This way, if jennie is outside the top 5, she will show as 5th; if she is inside the top 5, she will show in the appropriate position (because of the final order by rank).

    SELECT *
    FROM (SELECT u1.userid, u1.name, COUNT(u2.userid) + 1 AS rank
          FROM users u1
          LEFT JOIN users u2 ON u2.highscore > u1.highscore
          GROUP BY u1.userid) r
    ORDER BY rank <= 4 DESC, name = 'jennie' AND rank > 5 DESC, rank
    LIMIT 5;
    

    Output (for your sample data)

    userid  name    rank
    4       harry   1
    7       phil    2
    2       james   3
    1       sam     4
    6       jennie  7
    

    Demo on dbfiddle

    Output (for when jennie's score is 40)

    userid  name    rank
    4       harry   1
    7       phil    2
    6       jennie  3
    2       james   4
    1       sam     5
    

    Demo on dbfiddle

    Note I prefer to do ranking via LEFT JOIN queries, as variables can be problematic...