Calculate ranking change

old_blueyes · June 23, 2015

Hi,

I am trying to visually show the difference in "ranking" (rank) when I update the "score" value in my database.

I wish to output the current rank in one column then in the next column show the prev rank followed by maybe an icon (up/down arrows) or text (UP, DOWN, SAME) in another column as a visual key for changes in rank.

The score changes on a weekly basis, this is determined by a week no in my points database table:

POINTS

id | userID | week | score

1 | 1 | 1 | 4

2 | 2 | 1 | 1

3 | 3 | 1 | 3

4 | 1 | 2 | 0

5 | 2 | 2 | 4

6 | 3 | 2 | 1

Getting this ranking function to work is the final piece in the coding jigsaw on the project i'm working on.

Here is my current code (which works) but it works with only current ranking:

<?php $result = mysql_query("SELECT firstname
, surname
, week AS gameweek
, SUM(score) AS total
, tot.overall
FROM points
INNER JOIN users ON users.userID = points.userID
INNER JOIN (
    SELECT userID
      , SUM(score) as overall
    FROM points
    GROUP BY userID
    ) as tot ON points.userID = tot.userID
WHERE week = (SELECT MAX(week) FROM points)
GROUP BY points.userID
ORDER BY overall DESC") or die(mysql_error()); ?>

<?php $rank=0; $temp_score=0; ?>

<?php if(mysql_num_rows($result) > 0): ?>
<table>
    <tr>
        <th style="text-align:left;">CHANGE</th>
        <th style="text-align:left;">CURRENT RANK</th>
        <th style="text-align:left;">PREV RANK</th>
        <th style="text-align:left;">NAME</th>
        <th style="text-align:left;">PTS</th>
        <th style="text-align:left;">TW</th>
    <tr>
        <?php while($row = mysql_fetch_assoc($result)):
if($temp_score!=$row['overall'])
    $rank++;
        ?>
    <tr>
        <td><!-- CHANGE ICON OR TEXT --></td>
        <td><?php echo "#".$rank; ?></td>
        <td><!-- PREVIOUS RANK --></td>
        <td><?php echo $row['firstname']; ?> <?php echo $row['surname']; ?></td>
        <td style="font-weight: bold; color: #008AFF;"><?php echo $row['overall']; ?></td>
        <td><?php echo $row['total']; ?></td>
    </tr>
    <?php
$temp_score=$row['total'];
      endwhile; ?>
</table>
<?php endif; mysql_close(); ?>

Barand · June 24, 2015

It gets a bit more complicated but here goes

SELECT
    CASE 
        WHEN thiswk.rank > prevwk.rank THEN '^'
        WHEN thiswk.rank < prevwk.rank THEN 'v'
        ELSE '-'
        END as movement
  , thiswk.rank as this_rank
  , prevwk.rank as prev_rank
  , users.firstname
  , users.surname
  , tot.overall
  , thiswk.score
FROM
    users
LEFT JOIN
    (
    -- subquery for overall total
    SELECT userID
      , SUM(score) as overall
    FROM points
    GROUP BY userID
    ) as tot USING (userID)
LEFT JOIN
    (
    -- subquery for this week rank and score
    SELECT userID
    , @seqa := @seqa+1 as seq
    , @ranka := IF(score=@prevscorea, @ranka, @seqa) as rank
    , @prevscorea := score as score
    FROM points
        JOIN (SELECT @prevscorea:=null,@seqa:=0,@ranka:=null) as inita
    WHERE week = (SELECT MAX(week) FROM points)
    ORDER BY score DESC
    ) as thiswk USING (userID)
LEFT JOIN
    (
    -- subquery for prev week rank
    SELECT userID
    , @seqb := @seqb+1 as seq
    , @rankb := IF(score=@prevscoreb, @rankb, @seqb) as rank
    , @prevscoreb := score as score
    FROM points
        JOIN (SELECT @prevscoreb:=null,@seqb:=0,@rankb:=null) as inita
    WHERE week = (SELECT MAX(week)-1 FROM points)
    ORDER BY score DESC
    ) as prevwk USING (userID)
ORDER BY thiswk.score DESC ;

sample results

+----------+-----------+-----------+-----------+---------+---------+-------+
| movement | this_rank | prev_rank | firstname | surname | overall | score |
+----------+-----------+-----------+-----------+---------+---------+-------+
| -        | 1         | 1         | B         | Bbb     |       8 |     3 |
| -        | 2         | 2         | C         | Ccc     |       6 |     2 |
| v        | 3         | 4         | A         | Aaa     |       5 |     1 |
| ^        | 3         | 2         | D         | Ddd     |       5 |     1 |
+----------+-----------+-----------+-----------+---------+---------+-------+

old_blueyes · June 24, 2015

thanks, do i have to edit my points table i.e. add any extra colums for the above to work?

or change my php code, below??

<?php $rank=0; $temp_score=0; ?>

<?php if(mysql_num_rows($result) > 0): ?>
<table>
    <tr>
        <th style="text-align:left;">CHANGE</th>
        <th style="text-align:left;">CURRENT RANK</th>
        <th style="text-align:left;">PREV RANK</th>
        <th style="text-align:left;">NAME</th>
        <th style="text-align:left;">PTS</th>
        <th style="text-align:left;">TW</th>
    <tr>
        <?php while($row = mysql_fetch_assoc($result)):
if($temp_score!=$row['overall'])
    $rank++;
        ?>
    <tr>
        <td><!-- CHANGE ICON OR TEXT --></td>
        <td><?php echo "#".$rank; ?></td>
        <td><!-- PREVIOUS RANK --></td>
        <td><?php echo $row['firstname']; ?> <?php echo $row['surname']; ?></td>
        <td style="font-weight: bold; color: #008AFF;"><?php echo $row['overall']; ?></td>
        <td><?php echo $row['total']; ?></td>
    </tr>
    <?php
$temp_score=$row['total'];
      endwhile; ?>
</table>
<?php endif; mysql_close(); ?>

old_blueyes · June 24, 2015

I apologise for another reply, I cannot seem to find an edit function on my previous reply.

Ok, i have it working a little better now. Changes to my table structure arn't required.

It was a case, that there wasn't scores of all users in my table which i've since updated and it's fixed the shortcomings i had before.

However as a test i've added in a 2nd and 3rd week of points to try out the current rank/prev rank.

The good news, is the prev rank column does get populated however in both rank columns, but some NULL values are returned??

So they are blank, additionally the returned table also seems to be ordered by the weekly total and not the overall??

Thanks

Edited June 24, 2015 by old_blueyes

Barand · June 24, 2015

Change the ORDER BY to get a different order.

As for the null/blanks, I don't know without your data. Missing weeks or users maybe?

old_blueyes · June 24, 2015

not ideal, but i've attached my sql tables in a screenshot including the output with the proposed solution.

as far as I can see i'm not missing any users or any weeks, so I dont understand the reasons for the return of NULL

maybe you will see something im missing

Barand · June 25, 2015

I should have initialized @ranka and @rankb as 1 instead of NULL

SELECT
    CASE 
        WHEN thiswk.rank > prevwk.rank THEN 'v'
        WHEN thiswk.rank < prevwk.rank THEN '^'
        ELSE '-'
        END as movement
  , thiswk.rank as this_rank
  , prevwk.rank as prev_rank
  , users.firstname
  , users.surname
  , tot.overall
  , thiswk.score
FROM
    users
LEFT JOIN
    (
    -- subquery for overall total
    SELECT userID
      , SUM(score) as overall
    FROM points
    GROUP BY userID
    ) as tot USING (userID)
LEFT JOIN
    (
    -- subquery for this week rank and score
    SELECT userID
    , @seqa := @seqa+1 as seq
    , @ranka := IF(score=@prevscorea, @ranka, @seqa)+0 as rank
    , @prevscorea := score as score
    FROM points
        JOIN (SELECT @prevscorea:=null,@seqa:=0,@ranka:=1) as inita
    WHERE week = (SELECT MAX(week) FROM points)
    ORDER BY score DESC
    ) as thiswk USING (userID)
LEFT JOIN
    (
    -- subquery for prev week rank
    SELECT userID
    , @seqb := @seqb+1 as seq
    , @rankb := IF(score=@prevscoreb, @rankb, @seqb)+0 as rank
    , @prevscoreb := score as score
    FROM points
        JOIN (SELECT @prevscoreb:=null,@seqb:=0,@rankb:=1) as inita
    WHERE week = (SELECT MAX(week)-1 FROM points)
    ORDER BY score DESC
    ) as prevwk USING (userID)
ORDER BY overall DESC

+----------+-----------+-----------+-----------+---------+---------+-------+
| movement | this_rank | prev_rank | firstname | surname | overall | score |
+----------+-----------+-----------+-----------+---------+---------+-------+
| v        |         3 |         2 | Joe       | Bloggs  |      11 |     2 |
| -        |         1 |         1 | Adam      | Smith   |      11 |     3 |
| ^        |         1 |         4 | Tony      | Stark   |      10 |     3 |
| v        |         5 |         3 | Bruce     | Wayne   |       7 |     0 |
| ^        |         4 |         5 | Matt      | Murdoch |       5 |     1 |
+----------+-----------+-----------+-----------+---------+---------+-------+

Edited June 25, 2015 by Barand

old_blueyes · June 25, 2015

I should have initialized @ranka and @rankb as 1 instead of NULL

SELECT
    CASE 
        WHEN thiswk.rank > prevwk.rank THEN 'v'
        WHEN thiswk.rank < prevwk.rank THEN '^'
        ELSE '-'
        END as movement
  , thiswk.rank as this_rank
  , prevwk.rank as prev_rank
  , users.firstname
  , users.surname
  , tot.overall
  , thiswk.score
FROM
    users
LEFT JOIN
    (
    -- subquery for overall total
    SELECT userID
      , SUM(score) as overall
    FROM points
    GROUP BY userID
    ) as tot USING (userID)
LEFT JOIN
    (
    -- subquery for this week rank and score
    SELECT userID
    , @seqa := @seqa+1 as seq
    , @ranka := IF(score=@prevscorea, @ranka, @seqa)+0 as rank
    , @prevscorea := score as score
    FROM points
        JOIN (SELECT @prevscorea:=null,@seqa:=0,@ranka:=1) as inita
    WHERE week = (SELECT MAX(week) FROM points)
    ORDER BY score DESC
    ) as thiswk USING (userID)
LEFT JOIN
    (
    -- subquery for prev week rank
    SELECT userID
    , @seqb := @seqb+1 as seq
    , @rankb := IF(score=@prevscoreb, @rankb, @seqb)+0 as rank
    , @prevscoreb := score as score
    FROM points
        JOIN (SELECT @prevscoreb:=null,@seqb:=0,@rankb:=1) as inita
    WHERE week = (SELECT MAX(week)-1 FROM points)
    ORDER BY score DESC
    ) as prevwk USING (userID)
ORDER BY overall DESC

+----------+-----------+-----------+-----------+---------+---------+-------+
| movement | this_rank | prev_rank | firstname | surname | overall | score |
+----------+-----------+-----------+-----------+---------+---------+-------+
| v        |         3 |         2 | Joe       | Bloggs  |      11 |     2 |
| -        |         1 |         1 | Adam      | Smith   |      11 |     3 |
| ^        |         1 |         4 | Tony      | Stark   |      10 |     3 |
| v        |         5 |         3 | Bruce     | Wayne   |       7 |     0 |
| ^        |         4 |         5 | Matt      | Murdoch |       5 |     1 |
+----------+-----------+-----------+-----------+---------+---------+-------+

thanks for looking at it again, it's practically there now however this_rank seems to be tied to the end score column??

rather than the overall column, I don't know if the prev_rank column is as well, but you'll see that the highest scores in the end score column of 3 points

are given the position of 1st in the this_rank column, rather than the 11 points of the overall column.

i can see in the two subqueries there are the lines:

@prevscorea := score as score

ORDER BY score DESC

@prevscoreb := score as score

ORDER BY score DESC

i've tried changing these to overall but it doesn't get recognised??

Barand · June 25, 2015

thanks for looking at it again, it's practically there now however this_rank seems to be tied to the end score column??

rather than the overall column, I don't know if the prev_rank column is as well, but you'll see that the highest scores in the end score column of 3 points

are given the position of 1st in the this_rank column, rather than the 11 points of the overall column.

i can see in the two subqueries there are the lines:

@prevscorea := score as score

ORDER BY score DESC

@prevscoreb := score as score

ORDER BY score DESC

i've tried changing these to overall but it doesn't get recognised??

Yes, the rankings are based on the weekly scores and not the overall scores. You did not specify which it should be based on, so I chose weekly.

To base rank on the overall total will require you to completely rewrite of the query, not just a couple of changes to field names here and there, but the techniques used will be the same.

old_blueyes · June 25, 2015

Yes, the rankings are based on the weekly scores and not the overall scores. You did not specify which it should be based on, so I chose weekly.

To base rank on the overall total will require you to completely rewrite of the query, not just a couple of changes to field names here and there, but the techniques used will be the same.

I apologise, I probably should of been more specific in the first place.

I understand it's a big ask, but I don't suppose rewriting the query, is something you'll be able to help me with??

This is ultimately the last piece in the jigsaw of my project.

I would forever be in your debt!!

Barand · June 25, 2015

Forever? I hope not, I expect invoices to be settled within 30 days.

Barand · June 25, 2015

try this

SELECT
    CASE 
        WHEN thiswk.rank > prevwk.rank THEN 'v'
        WHEN thiswk.rank < prevwk.rank THEN '^'
        ELSE '-'
        END as movement
  , thiswk.rank as this_rank
  , prevwk.rank as prev_rank
  , users.firstname
  , users.surname
  , thiswk.total as overall
  , points.score
FROM
    users
    LEFT JOIN points USING (userID)
    LEFT JOIN
        (
        -- subquery for this week overall total and rank
        SELECT userID
          , @seqa:=@seqa+1 as seq
          , @ranka:=IF(total=@preva,@ranka,@seqa) as rank
          , @preva:=total as total
        FROM 
            (
            SELECT userID, SUM(score) as total
            FROM points
            GROUP BY userID
            ORDER BY total DESC
            ) as cum
            JOIN (SELECT @preva:=NULL,@ranka:=1,@seqa:=0) as init
    
        ) as thiswk USING (userID)
    LEFT JOIN
        (
        -- subquery for previous week overall total and rank
        SELECT userID
          , @seqb:=@seqb+1 as seq
          , @rankb:=IF(total=@prevb,@rankb,@seqb) as rank
          , @prevb:=total as total
        FROM 
            (
            SELECT userID, SUM(score) as total
            FROM points
            WHERE week < (SELECT MAX(week) FROM points)
            GROUP BY userID
            ORDER BY total DESC
            ) as cum
            JOIN (SELECT @prevb:=NULL,@rankb:=1,@seqb:=0) as init
        ) as prevwk USING (userID)
WHERE points.week = (SELECT MAX(week) FROM points)
ORDER BY overall DESC

+----------+-----------+-----------+-----------+---------+---------+-------+
| movement | this_rank | prev_rank | firstname | surname | overall | score |
+----------+-----------+-----------+-----------+---------+---------+-------+
| -        |         1 |         1 | Joe       | Bloggs  |      11 |     2 |
| ^        |         1 |         2 | Adam      | Smith   |      11 |     3 |
| -        |         3 |         3 | Tony      | Stark   |      10 |     3 |
| v        |         4 |         3 | Bruce     | Wayne   |       7 |     0 |
| -        |         5 |         5 | Matt      | Murdoch |       5 |     1 |
+----------+-----------+-----------+-----------+---------+---------+-------+

old_blueyes · June 27, 2015

great many thanks, forever in your debt

Sign In

Calculate ranking change

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information