Neil Aronson Neil Aronson - 7 months ago 25
SQL Question

Calculate the change in rank in MySQL

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

Answer

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`;
Comments