Order of specific Result

[xdentan]

If I have a table foo, and columns username and numbers, how can I get the order of a given username ordered by the numbers column? For example

john, 1
bill, 34
tom, -1
joe, 800

how would i find that bill is 2nd when the numbers are sorted in descending order?

thanks

[Roger_Ramjet]

You'll just have to query for the sorted data and find which row 'John' is in by looping through and counting. That is the only foolproof way that I know of.

[devinemke]

$result = mysql_query('SELECT username, number FROM foo ORDER BY number DESC') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$array[$row['username']] = $row['number'];}
echo 'bill is number ' . $array['bill'];

assuming all of the usernames are unique

[Lars_Berg]

select count(*) + 1
   from t
 where number >
  (select number
      from t
    where name = 'Bill')

[Roger_Ramjet]

Yep, that does it Lars. Nice one.

[xdentan]

thanks a ton Lars Berg. I had it enumerating all the values using a while loop like you guys postedd, but I had just over a thousand entries and the script was taking an extremly long time.

thanks again 🙂