Mysql Add Simple Rank Table

Question

Hi I want to simple add 1,2,3,4,5,6,7,8,9,10 rank.

My Output now gives the following:

USER ID | SCORE
2       | 10242
13231   | 3427
23732   | 3378
24638   | 2934
23468   | 1898

What I try to achieve is:

RANK | USER ID | SCORE
#1   | 2       | 10242
#2   | 13231   | 3427
#3   | 23732   | 3378
#4   | 24638   | 2934
#5   | 23468   | 1898

This is my php:

<?php $result = mysql_query("SELECT * FROM `users_score` ORDER BY `users_score`.`score` DESC LIMIT 0 , 10") or die(mysql_error());
if(mysql_num_rows($result) > 0): ?>
<table>
    <tr>
        <th style="text-align:left;">ID</th>
        <th style="text-align:left;">SCORE</th>
    <tr>
    <?php while($row = mysql_fetch_assoc($result)): ?>
    <tr>
        <td><?php echo $row['user_id']; ?></td>
        <td style="font-weight: bold; color: #008AFF;"><?php echo $row['score']; ?></td>
    </tr>
    <?php endwhile; ?>
</table>
<?php endif; ?>

Is there a simple like count function for this?

John Woo · Answer 1 · 2013-02-12T12:23:12.907

You can do that in query by using variables.

SELECT  @row:=@row+1 as RankNo,
        a.UserID,
        a.Score
FROM    tableName a, (SELECT @row:=0) b
ORDER   BY a.Score DESC

SQLFiddle Demo

but it has a DownSide, it doesn't handle tie score

if you want to add # on the rank, cancatenate the RankNo with#`

UPDATE 1

SELECT  CONCAT('#', @row:=@row+1) as RankNo,
        a.UserID,
        a.Score
FROM    tableName a, (SELECT @row:=0) b
ORDER   BY a.Score DESC

SQLFiddle Demo

Robin Castlin · Answer 2 · 2013-02-12T12:17:47.633

1

SELECT (@rank := @rank + 1) AS rank, user_id, score
FROM (  SELECT user_id, score
        FROM scores, (SELECT @rank := 0) AS vars
        ORDER BY score DESC) AS h

SQLFiddle

edited Feb 12 '13 at 12:17

answered Feb 12 '13 at 12:04

Robin Castlin

10,956
1
28
44

I don't think anything's gained by the nested query. Am I missing something? – StarNamer Feb 12 '13 at 12:23
Might not be in this case, but there are some such as `GROUP BY` that requires ordering and nesting 1st, then `SELECT`. Just playing it safe but doing this :) – Robin Castlin Feb 12 '13 at 12:25

score 0 · Answer 3 · edited May 23 '17 at 11:56

What you are looking for is an equivalent to MS SQL's row_number() function. This has been answered before, e.g. here or here

The answer is to use something like:

SELECT @rn:=@rn+1 as RANK, ID, SCORE
       FROM `users_score`, (SELECT @rn:=0) as rn
       ORDER BY `users_score`.`score` DESC LIMIT 0

Update

Just for interest, handling matching scores can be done in SQL:

SELECT  @rn := @rn + case (@ls-SCORE) when 0 then 0 else @s end as RANK,
        ID,
        SCORE,
        @s := case (@ls-SCORE) when 0 then @s+1 else 1 end as STEPSIZE,
        @ls := SCORE as LASTSCORE
FROM    `users_score`.`score` a,
        (SELECT @rn := 0) b,
        (SELECT @ls := max(SCORE)+1 FROM `users_score`.`score`) c,
        (SELECT @s := 1) d
ORDER   BY a.Score DESC LIMIT 0

However, if you want to do this, then it's easier in the PHP code.

score 0 · Accepted Answer · answered Feb 12 '13 at 12:25

If you just want changes in your php here is the code...

<?php 
$result = mysql_query("SELECT * FROM `users_score` ORDER BY `users_score`.`score` DESC LIMIT 0 ,10")  or die(mysql_error());

$rank=0;
$temp_score=0;

if(mysql_num_rows($result) > 0): ?>
<table>
    <tr>
        <th style="text-align:left;">RANK</th>
        <th style="text-align:left;">ID</th>
        <th style="text-align:left;">SCORE</th>
    <tr>
    <?php while($row = mysql_fetch_assoc($result)): 
         if($temp_score!=$row['score'])
             $rank++;
    ?>
    <tr>
     <td><?php echo "#".$rank; ?></td>
     <td><?php echo $row['user_id']; ?></td>
     <td style="font-weight: bold; color: #008AFF;"><?php echo $row['score']; ?></td>
    </tr>
    <?php 
         $temp_score=$row['score];
      endwhile; ?>
</table>
<?php endif; ?>

Mysql Add Simple Rank Table

4 Answers4