MySQL leaderboard with rank

Question

I have a leaderboard where i want to give each row a rank for how many points they have. This is the code i have so far. It works fine but when two players have the same amount of points, the rank is the same, for example if there are 2/2 players in the DB where they both have 100 points, both their ranks are 1.

I would like to even if they have same amount of points to give one of them a higher rank, so it would be 1,2.

SELECT id, leaderfirst.pictureid, leaderfirst.point, FIND_IN_SET( leaderfirst.point, (
SELECT GROUP_CONCAT( leaderfirst.point
ORDER BY leaderfirst.point DESC ) 
FROM leaderfirst )
) AS rank
FROM leaderfirst

I was hoping this question was about a leaderboard of the best MySQL architects and database developers. `:-(` — Martin, Aug 22 '17 at 19:07
What you want to do is define what places two equal scores first and second? for example is it time spent? surname? What criteria (aside from points) can the ranks be ordered, without that your question can not really be answered. — Martin, Aug 22 '17 at 19:09
Well its just ordered by points i dont care like who is placed as number one and two if they have equal points. — Jeppe Schultz Rasmussen, Aug 22 '17 at 19:34

score 0 · Answer 1 · edited Aug 22 '17 at 19:18

0

leaderboard =

SELECT id,
 leaderfirst.pictureid,
 leaderfirst.point,
 FIND_IN_SET( leaderfirst.point, 
       ( SELECT GROUP_CONCAT( 
      leaderfirst.point ORDER BY leaderfirst.point DESC ,id) 
         FROM leaderfirst ) ) AS rank 
FROM leaderfirst

Try this.

edited Aug 22 '17 at 19:18

Martin

22,212
11
70
132

answered Aug 22 '17 at 17:26

Nishant Pal

1
1

That gave the same result as the one i currently have :/ – Jeppe Schultz Rasmussen Aug 22 '17 at 17:29

Martin · Answer 2 · 2017-08-24T08:16:40.150

What you want here is an increment counter in the SQL:

There are two ways of doing this:

First:

set @rownum := 0;

And then

SELECT id, leaderfirst.pictureid, leaderfirst.point, 
   FIND_IN_SET( leaderfirst.point, 
   ( SELECT GROUP_CONCAT( 
       leaderfirst.point ORDER BY leaderfirst.point DESC 
       ) 
    FROM leaderfirst )
    ) AS rank, 
          @rownum := @rownum + 1 AS row_number
FROM leaderfirst
ORDER BY row_number

Or:

Combining the first two queries together you can use :

SELECT id, leaderfirst.pictureid, leaderfirst.point, 
   FIND_IN_SET( leaderfirst.point, 
   ( SELECT GROUP_CONCAT( 
       leaderfirst.point ORDER BY leaderfirst.point DESC 
       ) 
    FROM leaderfirst )
    ) AS rank, 
          @rownum := @rownum + 1 AS row_number
FROM leaderfirst
CROSS JOIN (select @rownum := 0) r
ORDER BY row_number

Source: This answer here.

There are also various variaton on this theme.

Solution 2:

simply ignore trying to generate this data with your MySQL; you said you don't care about order, simply the auto-increment nature of the resultant ranking; therefore you can do something like this if you're using PHP (and similar work in other languages):

Pseudo-code below:

$mysqlDataOutput = mysql result data from your SQL . "ORDER BY rank DESC";

$counter = 0;
foreach($mysqlDataOutput as $resultRow){

     print $counter.") ".resultRow['points']. " -
           <img src='".$resultRow['pictureid']."' alt=''>";
     $counter++;
}
unset($mysqlDataOutput, $resultRow);

If you want to order the other way around (lowest rank first) then simply invert your SQL ORDER BY and set:

$counter = count($mysqlDataOutput);
foreach(){
    ...
    $coounter--;
    }

This just adds a row_number that goes up like 1,2,3... its not effected by the points in any way? — Jeppe Schultz Rasmussen, Aug 22 '17 at 19:31
@JeppeSchultzRasmussen it answers what you want, it prevents having duplicate rank numbers, such as 2/2, instead forcing 1/2, then 2/2, ordered by the rank rather than a listing that contains duplicate values. Why does this not fit what you;re trying to achieve. If this doesn't fit, then please consider editing and clarifying your question. — Martin, Aug 22 '17 at 22:14

MySQL leaderboard with rank

2 Answers2

First:

Or:

Solution 2: