Jump to content

Calculate ranking change


old_blueyes
Go to solution Solved by Barand,

Recommended Posts

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(); ?>
Link to comment
Share on other sites

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 |
+----------+-----------+-----------+-----------+---------+---------+-------+
  • Like 1
Link to comment
Share on other sites

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(); ?>
Link to comment
Share on other sites

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 by old_blueyes
Link to comment
Share on other sites

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

 

post-172397-0-08205800-1435187581_thumb.jpg

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

 

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??

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!!

Link to comment
Share on other sites

  • Solution

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 |
+----------+-----------+-----------+-----------+---------+---------+-------+
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.