MySQL/php help

[DavidNels]

Hey guys,

Quick question about mysql, let's say I want to pull data from a table, limit 100 results, where 'score' is highest, from high to low, I'd get results like this, perhaps:

name | score
------------------
tom | 50
joe | 45
bill | 40
.......

and so on. Now, what I would like to be able to do is easily find a given name's 'rank', which would be out of 100, sorting 'score' as I did above. 'tom' would be #1, for instance, Joe #2, and Bill #3.

What I'm wondering is, is there an easy way to make a mysql request to retrieve 'where name = "tom"' and also retrieve the number of rows from the top that the entry sits at, ordered by score highest to lowest.

Any help is appreciated, thanks a lot! :)

David

[dclamp]

a query like this is what your looking for:

"SELECT name, score FROM mytable ORDER BY score ASC LIMIT 100"

Select name and score from table ordering them by score in ascending order from the first 100 rows.

[DavidNels]

Right, but I need to return the number which it ranks from that query. :)

[dclamp]

use a while loop with a count increment


$number = 1;
while(blahblahblah) {
echo $number;
// blah
$number++;
}

[penagate]

set @row = 0;
select
@row := @row+1 'Index',
name 'Name',
score 'Score'
order by score desc
limit 100;