MySQL Forums
Forum List  »  Newbie

how to get the rownumber of a special row
Posted by: Christopher Steinfeldt
Date: April 08, 2006 12:34PM

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.

Options: ReplyQuote

Written By
how to get the rownumber of a special row
April 08, 2006 12:34PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.