Calculate the change in rank in MySQL

Question

I've created two views in my database that each contain a list of people ordered by 'br_engagement' for different time periods. I want to calculate the change in rank from the old time period to the new time period. I think this would be easy enough if I could store variables in views, however I'm faced with the challenge of how to perform a query where I calculate the change in rank when I can only get it in a session variable as found by this technique.

This is the view of the top 10 I have been able to create for the new time period:

+---------+----------------------+
|  name   | total_br_engagements |
+---------+----------------------+
| Person1 |                97553 |
| Person2 |                73576 |
| Person3 |                15245 |
+---------+----------------------+

In order to add a ranking, I do SELECT @rank := @rank +1 AS rank, name, total_br_engagements FROM (SELECT * FROM top10current) t1, (SELECT @rank :=0)t2

Now the question is how to find the change in rank from one view (top10old) to a new view (top10current). My first attempt at it was the following:

select @rank:=@rank+1 as rank, old.oldrank, (old.oldrank-rank), new.name,  new.total_br_engagements 
    from ((select name, total_br_engagements from top10current) t1, (select @rank:=0) t2)  new
left join (
    SELECT @rn:=@rn+1 as `oldrank`, name, side, party, total_br_posts, total_br_engagements
    from (select name, side, party, total_br_posts, total_br_engagements from top10old) t01, (select @rn:=0) t02) old 
on new.name=old.name

but I get an error near 'new left join ( SELECT @rn:=@rn+1 as oldrank, name'.

Is there a simpler way to do this, or am I on the right track?

UPDATE: I modified the code to use new_rank as the name of my current top 10 but I get the same error. I also took out some unnecessary variable names in the 2nd table. Here is the new code:

select @rank:=@rank+1 as rank, old.oldrank, (old.oldrank-rank), new_rank.name, new_rank.total_br_engagements 
    from (
        (select name, total_br_engagements from top10current) t1, (select @rank:=0) t2)  new_rank
left join (
    SELECT @rn:=@rn+1 as `oldrank`, name, side, party, total_br_posts, total_br_engagements
        from (select name, total_br_engagements from top10old) t01, (select @rn:=0) t02) old 
on new_rank.name=old.name

Try using back ticks around `new` key word in `new left join`. Example ***\`new\` left join***. Or else use a different alias as `new_rank` — Ravinder Reddy, Apr 27 '16 at 12:48
Thanks, I've tried that but am getting the same error about the second session variable with new_rank as the alias name. — Neil Aronson, Apr 27 '16 at 13:02

score 0 · Accepted Answer · 2016-04-27T13:37:35.787

This is my best attempt at your SQL code, please don't bite if I'm wrong T_T

SELECT    @rank := @rank + 1       AS `rank`, 
          old.oldrank              AS `oldrank`, 
          old.oldrank - @rank       AS `rankchange`, 
          new.name                 AS `name`, 
          new.total_br_engagements AS `total_br_engagements` 
FROM      ( 
          ( 
                 SELECT name, 
                        total_br_engagements 
                 FROM   top10current) AS t1, 
          ( 
                 SELECT @rank := 0) AS new) 
LEFT JOIN 
          ( 
                 SELECT @rn := @rn + 1 AS `oldrank`, 
                        `t2`.`name`, 
                        `t2`.`side`, 
                        `t2`.`party`, 
                        `t2`.`total_br_posts`, 
                        `t2`.`total_br_engagements` 
                 FROM   ( 
                               SELECT `name`, 
                                      `side`, 
                                      `party`, 
                                      `total_br_posts`, 
                                      `total_br_engagements` 
                               FROM   top10old) AS t2, 
                        ( 
                               SELECT @rn := 0) AS old
                        ) AS t3
ON        `new`.`name` = `old`.`name`;

No biting! It unfortunately doesn't work though; I get an error #1248 - Every derived table must have its own alias. Looking at the code, I see you've removed the t2 and t02 aliases, which I think are necessary to get the ranking to work. Those are just there because MySQL requires an alias after the (select @rn:=0) as far as I can understand — Neil Aronson, Apr 27 '16 at 12:52
Ah indeed you have, but I need to join the tables including the @rn rank variable. Thus when I try that I get "Unknown column 'old.oldrank' in 'field list'" because I need to calculate the difference between the old and new ranks. The rank part has to be part of a fused table so that I can directly compare rank in one to another — Neil Aronson, Apr 27 '16 at 13:14
Ok your idea was right, I just had to play around with the alias names! Also had to change the `oldrank - rank` to `oldrank - @rank`. Brilliant — Neil Aronson, Apr 27 '16 at 13:30

Neil Aronson · Answer 2 · 2016-04-27T13:57:26.723

Based on @LFlare 's answer.

SELECT    @rank := @rank + 1       AS `rank`, 
          old.oldrank              AS `oldrank`, 
          (old.oldrank- @rank) as `change`,
          `new`.name                 AS `name`, 
          `new`.total_br_engagements AS `total_br_engagements` 
FROM      ( 
          ( 
                 SELECT name, 
                        total_br_engagements 
                 FROM   top10current) AS `new`, 
          ( 
                 SELECT @rank := 0) AS t1) 
LEFT JOIN 
          ( 
                 SELECT @rn := @rn + 1 AS `oldrank`, 
                        `old`.`name`, 
                        `old`.`total_br_engagements` 
                 FROM   ( 
                               SELECT `name`, 
                                      `total_br_engagements` 
                               FROM   top10old) AS old2, 
                        ( 
                               SELECT @rn := 0) AS t2
                        ) AS old
ON        `new`.`name` = `old`.`name`

Calculate the change in rank in MySQL

2 Answers2