Hello out there,
I have a My SQL database with scores in a game, and I would like to select the top 10 from the database, and also numbers 11-15. In SQL Server I would do SELECT TOP 10 etc., but this does not work in MySQL..... Anybody? And does anyone know how to receive the numbers 11-15. And eh, yes, I am making an overall standing :-)
Joyce
By the way, my solution for now is:
$i = 0;
while (($row = mysql_fetch_row($result)) AND ($result > 0))
{
$i++;
if($i <=10)
{
echo "<LI> $row[1] $row[2] $row[3]<BR>";
}
}
This solution works, but I think it is better to make my query more specific.
Joyce
or this
$data = mysql_query("SELECT * FROM table
ORDER by rank DESC
LIMIT 10");
That's it! I suppose I can do something like that with 10-15 as well?
$data = mysql_query("SELECT * FROM table
ORDER by rank DESC
LIMIT 11 , 5");
will limit the result to 5 rows, starting with row 11.
Great, thanks for helping!
I too was going to suggest using LIMIT... however, I have questions about that. If you look at this page, you'll see the following statement:
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
What does this mean? Does it mean that MySQL will return the first # lines, then sort the results? If that's the case, then it would seem using ORDER BY with LIMIT does not work the way you would intend it to. Or am I missing something? Am I misreading that statement?
What does this mean? Does it mean that MySQL will return the first # lines, then sort the results? If that's the case, then it would seem using ORDER BY with LIMIT does not work the way you would intend it to. Or am I missing something? Am I misreading that statement?
It means: MySQL will search the first top ten, then sort these and stop the sorting, all other rows wont be sorted anymore
it is good so,
think again when you have e.g 1000000 rows, and you want only the top ten, mysql gives you the top ten, after this , it sorts the rest again , 
So... does that mean that you might not actually get the top x records? The way it's worded, it will first give you the first x records in the query, then perform the ORDER BY. I would think you wanted the ORDER BY to run first, then have it give you the first x records -- for example, to return the top 3 scores of your bowling tournament.
See what I'm saying? I'm just concerned that it will grab the first 3 records in your bowling tournament table, then sort them by score. That doesn't mean you would get your best 3 scores out of all 52 players.
Maybe I'm beating a dead horse here, but I just want to be sure I understand how LIMIT and ORDER BY work together.
What I'm hoping it means is that it will perform the sort algorithm (which I believe is qsort), and as soon as it is sure that it has the top X records as specified by the LIMIT keyword, it stops the sorting, so as to return the results as quickly as possible. Is this correct?
no no no, it performs at beginning GROUP BY. That means, all the data will be sorted, but as immediate it got his10th sorted, the sorting will be stopped right there, it means, up 11th to the end, nothing will be sorted anymore
What I'm hoping it means is that it will perform the sort algorithm (which I believe is qsort), and as soon as it is sure that it has the top X records as specified by the LIMIT keyword, it stops the sorting, so as to return the results as quickly as possible. Is this correct?
100% correct
Excellent... 