List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 23 1999 10:51am
Subject:Re: sql question
View as plain text  
"S. Young" wrote:
> 
> Hello.
> 
> I am using mysql to run a game.  One of the tables has the
> columns "player" and "score".  A few times a day I run a script that
> (using another table) calculates everybody's score.
> 
> I would like to add a column "rank" to the table so that the player
> with the highest score will have '1' in the rank column, the next
> player will have '2', etc.  I want this column because it will make it
> more efficient for me to find a player based on his rank and vice
> versa.
> 
> Is there a sql command that I can use in order to update the rank
> column?
> 
> TIA,
> 
> Sara

You probably want to do the following (I am making certain assumptions
about your tables):

  alter table player add rank int not null ;
  create table tmp_player (id int , rank int auto_increment);
  insert into tmp_player (id) select id from player order by score
asc;    
  replace into player (id,rand) select id,rank from tmp_player;
  drop tmp_player;

This is untested, just straight out of by head. Make a backup first. I
hope you have a development machine or database at least to try it on
before you do it on the production machine. You may need to make some
changes if my assumptions are not correct.

You actually probably do not need to bother with this if all you want is
the order of players based on score. You can just use order by and
limit.

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
sql questionS. Young23 Jun
  • Re: sql questionSasha Pachev23 Jun