ReSorting users Ranking sorted by points

Question

I've a table called "Ranking" which basically saves users ranking position sorted by their "points". It's:

*Id - Integer - A.I. (It's the position. 1 = first one. Highest points)
*IdUser - Integer
*PointsNow - Integer
*PointsBefore - Integer
*PositionBefore - Integer

What I'm trying to achieve is to have some method to know if an user went into a highest rank position or if he's lower now.

So for example, those are some entries:

1 | 2236 | 41 | 0 | 0

2 | 551 | 40 | 0 | 0

3 | 1557 | 34 | 0 | 0

So I though that I could achieve this doing this:

*Delete all entries

*Reset the autoincrement to 1

*Copy PointsNow to PointsBefore and Id to PositionBefore.

I'm not sure if I should do it this way, or if there's a more logical option. I'd appreciate some help.

Thanks

You can use a trigger to update PositionBefore, i.e. trigger before update PositionNow column, where to set PositionBefore column = PositionNow; and create a view to display the info as you need. — Anda Iancu, Jan 28 '13 at 12:21
Don't store that kind of information. Just calculate it on-the-fly. — Strawberry, Jan 28 '13 at 12:24

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Try this:

select @rownum:=@rownum+1 `rank`, u.idUser 
from User u, (SELECT @rownum:=0) r 
order by PointsNow desc;

If you just want to order the user based on the ranking:

SELECT * FROM User
ORDER BY PointsNow DESC;

Sample data:

CREATE TABLE User(
IdUser Integer primary key,
PointsNow Integer,
PointsBefore Integer,
PositionBefore  Integer);

INSERT INTO User(IdUser,PointsNow,PointsBefore,PositionBefore)
VALUES (2236,41,0,0),(551,40,0,0),(1557,34,0,0);

SQL FIDDLE DEMO

Important to note is that I have omitted the id column. Use triggers to automate the query. See for more info on the ranking calculation. MySQL update statement to store ranking positions.

ReSorting users Ranking sorted by points

1 Answers1