retrieve each row's position (mysql)

[nunomira]

hi,

Supposing I have a score's table, something like:

45, spiderman, 123
23, batman, 77
76, catwoman, 98
34, Wolverine, 156

which is: score_id, nickname, max points

How would I get something like (if it's possible):

1, Wolverine, 156
2, spiderman, 123
3, catwoman, 98
4, batman, 77

i.e., I want to get the position they are at, the numbers: 1, 2, 3, 4, which will depend on the score.

In order to have just

Wolverine, 156
spiderman, 123
catwoman, 98
batman, 77

I'd do something like:

SELECT nickname, max_points
FROM `points`
ORDER BY max_points DESC
LIMIT 4 

I know there are workarounds to do this using PHP (a variable inside a while($row = mysql_fetch_assoc($result))), but I need it to appear in the recordset directly, using SQL only.
Is it possible?

[laserlight]

I have never tried this before (the workaround you describe has always been enough for me), but one idea you could try would be to create a temporary table with the result set. An autoincrement would then give you the numbering you want.

This seems like a poor man's solution though, perhaps a database guru would be able to give you a better answer.

That said, why do you need to have it done in the database alone?

[nunomira]

hi laserlight, thank you.

I'm going to try what you suggested.

I'm sending data to Flash via amfphp, i.e., I'm sending the $result directly.
Unless I could modify the result, where $result = mysql_query ($query) - which I have no idea whther can be done or not - I can't think of another solution.

I'm still a newbie at amfphp, so I may be going the wrong way...