MySQL: set field to position in order by clause

I'm designing a leader-board where we display each users ranking along with their change in ranking from the previous day. The ranking is found by simply ordering the users by the points column.

To find the change in ranking from the previous day, I'm thinking I'll have to have a cron job that stores the users rank every day at midnight.

Is it possible to write this cron job to use only one sql query? I need to design a query that stores every user's rank in the previousRank column. Any help? I'd like to do this in a constan开发者_如何学Ct amount of queries, not needing to process each row with PHP.

My suggestion:

Create a previous_points column and then issue this simple query each night:

UPDATE table SET previous_points = points;

Then to display ranks by the current or previous day, simply use points or previous_points, respectively.

Edit

You could use temporary tables to make use of this data.

Here is an example:

1: Make a temporary table populated with today's rankings:

SET @rank := 0;
CREATE TEMPORARY TABLE ranks_today
    (id INT(8), rank INT(6), previous_rank INT(6))
    SELECT id, @rank:= @rank + 1 AS rank
    FROM users
    ORDER BY points DESC;

2: A temporary table for yesterday's rankings:

SET @rank := 0;
CREATE TEMPORARY TABLE ranks_yesterday
    (id INT(8), rank INT(6))
    SELECT id, @rank:= @rank + 1 AS rank
    FROM users
    ORDER BY previous_points DESC;

3: Now we'll copy yesterday's rankings to today's table for convenience:

UPDATE ranks_today, ranks_yesterday
SET ranks_today.previous_rank = ranks_yesterday.rank
WHERE ranks_today.id = ranks_yesterday.id;

4: Finally, a query that shows the user's name, ranks for today and yesterday as well as the change:

SELECT u.name, t.rank, t.previous_rank, t.previous_rank - t.rank `change`
FROM ranks_today t, users u
WHERE u.id = t.id;

You could optionally create rank and previous_rank columns in your users table if you want and update those in step 3.

Here is sample output from the query:

name        rank    previous_rank    change
----        ----    -------------    ------
Fred        1       2                1
Betty       2       5                3
Barney      3       3                0
Bam Bam     4       1                -3
Wilma       5       4                -1