MySQL - Get position of sorted rows?

[duncanhall]

I have a table which stores high-scores, along with player ids. I want to be able to extract a record by a players id, and then get the rank, or position of their score in the table.

"SELECT * FROM `high_scores` ORDER BY `game_score` DESC";

Will give me the results ordered by score, highest to lowest, but how can I then get a record from it and determine where in the table this score is placed?

[simplistik]

ummm... do what? what do you mean "where in the table the score is placed"?

[rschoenbach]

no i believe he is looking for something like if he is in the top 10 best points per game show what rank he is..

i dont know if this is the most effective way.. but try something like this

Select * from whatever table

$x=0
while($row = mysql_fetch_array($result) {
if ($user == $row['user']) {
echo "$user is rank $x out of " . count($result);
break;
} else {
$x++
}
} 

[duncanhall]

I didn't think I'd done a very good job of explaining so I'll see if I can do better.

A have a table called `high_scores` which, among other things, has columns for 'id', 'score' and 'player_id'.

id-------score-------player_id
-----------------------------------
0-------1500-------54
1-------2800-------222
2-------200--------11
3-------5000-------514

Lets say I want to select the details for the user with player_id=222. I can get his score value, and the id unique to his score entry.

Or if I want to display all scores and their players in order of highest score to lowest, I can do that as mentioned above.

But what if I want to say

"Select the score that is 59th in the score column"

But not merely the 59th entry in my table; rather 59th down the list of ORDERED scores.

Basically I want to be able to say "you are in Nth" position, purely based on the players score against all other scores.

[rschoenbach]

I didn't think I'd done a very good job of explaining so I'll see if I can do better.

A have a table called `high_scores` which, among other things, has columns for 'id', 'score' and 'player_id'.

id-------score-------player_id
-----------------------------------
0-------1500-------54
1-------2800-------222
2-------200--------11
3-------5000-------514

Lets say I want to select the details for the user with player_id=222. I can get his score value, and the id unique to his score entry.

Or if I want to display all scores and their players in order of highest score to lowest, I can do that as mentioned above.

But what if I want to say

"Select the score that is 59th in the score column"

But not merely the 59th entry in my table; rather 59th down the list of ORDERED scores.

Basically I want to be able to say "you are in Nth" position, purely based on the players score against all other scores.

switch the above code to something like this

$x=0
while($row = mysql_fetch_array($result) {
if (NthPerson == $x) {
echo "You have selected the Nthperson;
break;
} else {
$x++
}
} 

[duncanhall]

Thanks for the reply, but I was rather hoping for a MySQL solution. Correct me if I'm wrong, but if my table held 10,000 high scores then putting that into a PHP loop might be pretty intensive compared to a nice MySQL query.

Also, as well as saying "Select the score which is rank N"

I would like to be able to get a 'rank' given a particular score.

[simplistik]

has to be done via server-side code... can't be done w/ just mySQL the way rschoenbach and x would be defined as the players' rank

[duncanhall]

Ok, so either my explanation is still pretty useless, or it's simply not possible.

My MySQL knowledge is pretty rudimentary at best, but:

I know you can sort tables by specific criteria.
I'm 'fairly sure' you can create tables on the fly for query purposes.

So then, ignoring everything else, what if my question was just this:

How can I :

Select everything from table A, ordered by a certain column.
Create a temporary table B and insert table A's ORDERED content into it
Query table B (I'm assuming the row indexes will now be equal to the order specified by the first query in step 1).

This still sounds overly complicate but I don't seem to be getting anywhere with this.


Edit: Just read above. Fair play, I guess I shall concede, but it still feels like it should be possible.

[rschoenbach]

I know u can do temp tables with SQL not sure if it is possible with mySQL but it would be the best way to do it with a temp table.

[rschoenbach]

There might be another way to do it.

If you sort a table based on whatever you could use the limit command to start at a specific record.

Like

Select * from table Limit 10,20

would start at record 11 and show upto record 30.

In this example you could make Nth be the limit to start at.

[ramie]

Your correct dunchanhall it can be done with sql, it's an annoyance in mySQL tho because it does not have a handy row number function available for counting the rows of a query, so we have to do it the hard way.

Create a derived table that has the rank for each score and query that, logically the rank is the top down count of an ORDER BY score DESC query on a table like the following...

id  score   player_id
1   1000    4
2   2000    3
3   3000    2
4   4000    1

We want to know what is ranked 3rd (we know its id is 2 and score is 2000 at a glance), to do that in mySQL we count the rows, store that count as a new column called rank and query the new table by rank.

SET @rownum := 0; # create the variable

SELECT * FROM (
  SELECT @rownum := @rownum+1 AS rank, id, score, player_id # increment the row count and store as rank for this result
  FROM scores 
  ORDER BY score DESC
) AS derived_table WHERE rank = 3; # AS derived_table is the alias for the new derived table!

would return

rank   id  score   player_id
3  	2  2000    3

If this dosnt make sense let me know and 'll break the SQL down more, I tested this on mySQL 5.0

[simplistik]

^^ wow... didn't know that... though it looks pretty ugly to do.

[ramie]

its far from ugly and cuts a lot of server side nonsense out, its basically just a nested query if you look at it closely, the AS statements are aliases for the things we create (rank and derived table), @rownum is just incremented when your running the standard query and stored as rank in the new table, then you query that.

[eirche]

supposed the table is ordered `score` desc, `id` asc


get the 10th rank from the top

"select * 
from `scoretable` 
order by `score` desc, `id` asc 
limit 9, 1"; 

get rank number by a given score 2000

"select count(*)+1 as rank 
from `scoretable` 
where `score` > 2000";
// what if 2000 is non-existent, is it still considered to have a rank number 

ramie's reply uses multi-query which requires php 5. that's fine when you design the app for own use. but if you are making an app for other ppl to use, lowered the requirement to php 4.4, mysql 4.1 or something like that.

assume there is no function rank() in mysql that will do
select *, rank() from scoretable order by score desc, id asc
this is what i came up. everything is in a single sql statement.


get rank number by a given scoreid 123, assume possible identical scores

"select a.rank+b.rank as rank
from (SELECT count(*)+1 as rank FROM scoretable where score > (select score from scoretable where id = 123)) as a
    straight_join
    (SELECT count(*) as rank FROM scoretable where score = (select score from scoretable where id = 123) and id < 123 ) as b" 

if there is a better solution i would love learn

[duncanhall]

Awesome, thanks for the help guys, I knew there was a more sensible way of doing things. I wont be able to work on this on until Wednesday, but eirches example seems to make perfect sense (I'm resticted to PHP 4).

Cheers!