select from ... syntax

hakiepoo

Hi,
Can anyone explain to me how i can retrieve the 5th highest number in a column in a table. Basically, i've got a game, and after the game has finished i want to check whether the players score is good enough to enter the high score. Therefor i need to find out what the 5th highest number in the specific column.

I thought about trying something like this:

$highScore = mysql_query("SELECT * FROM $table ORDER BY score") or die(mysql_error());
$rows=mysql_fetch_array($highScore);
$score=$rows["score"];

echo $score;

but i need something more in the first line. Can anyone help please?

In advance, thanks

bogu

You could select the first five score from your table using ORDER BY and LIMIT:

$highScore = mysql_query("SELECT * FROM $table ORDER BY score DESC LIMIT 0,5") or die(mysql_error()); 
$i=0;
while($rows=mysql_fetch_array($highScore))
          $score[$i++]=$rows["score"];

In array $score you shold have the first 5 score.

xblue

you can also extract the fifth-highest value only with limit (I understand this is the only one you need to compare it with the current score?)

SELECT * FROM $table ORDER BY score DESC LIMIT 4,1

4 = offset, will not return the first 4 row
1 = length, will return one row: the 5th

hakiepoo

thanks!!
i got the number i wanted.