how to get the rownumber of a special row

[Christopher Steinfeldt]

Hi i actualy have a problem with my Database structure. I'm a programmer on an online game. No cause the old code is where dirty, i trie to clean it up.

Today my job is the toplist.

But there is a small problem. I got about 1000 Users. Every hour i create a new toplist. This is done by an insert select call. I order the users directly y their points. Also i have an auto_increment value in the table. So after the truncate and insert select each row has a number containing users position in toplist. When now a special user wants to get is postion and 20 People around him. I first ask for his position and then call the persons around him by limiting the select with limit pos-x,2*x

So he is in the middle of his individual toplist.

This works fine. But only if i want to get his position by points. In all other cases there is no way to find out his real position.

An example. I want to know on which position he is, when i ask for his age. I can't create a second auto_increment which i fill up with an SELECT ordered by age, cause there is only one allowed.

So from my point of view there are 3 ways to fix this.

1) the way i use actually SELECT the toplist ordered by age and then rewrite each row back and manual(by php) adding his position.
2) if there is a value while inserting more than one row which tells me about which tells me how many rows have been inserted before
3) i can do a SELECT which returns me the number of the row the user has, when i would order the Table by age.

Would be nice, when someone could tell me, there is a variable i need for 2/3 cause 1 is a very slow way, cause php has to do the job and inserting a lot of rows.