What version of MySQL are you using? The order by and limit clauses should work the way you want them to, not the way you are describing -- i.e. it should order first and then return the top 100 rows. I use this kind of sql statement all the time in the reports I write. I'm not sure if any older versions of MySQL had a bug with order by and limit clauses, though...
--jeff ----- Original Message ----- From: "Michelle de Beer" <[EMAIL PROTECTED]> To: "Alliax" <[EMAIL PROTECTED]>; "mysql list" <[EMAIL PROTECTED]> Sent: Thursday, December 05, 2002 4:39 PM Subject: RE: Limit and Order by > > you mean mySQL does the search on 100 first entries > > and then order the > > results, instead of getting the results and > > returning only the first 100 ? > > If yes I too would like to know what's the right way > > to do it in SQL then ? > > That is correct. This is what I have: > > One table with 1000 records. Two columns, students > names (name) and the total score of a test (total) > > When I try this query, I sort by the highest total: > Select * from mytable ORDER by total desc > > When on the other hand I use this query, mySQL only > takes the first hundred rows and sorts them: > Select * from mytable ORDER by total desc limit 0, 100 > > The problem is that there can be a very high score at > row 234, that should be in the top one hundred result, > but this is left out. > > So the question is, can I limit the result and get all > the highest scores from my table with a query, or do I > have to add a counter in my PHP-script that breaks out > of the loop at 100? > > Thanks, > // Michelle > sql, query > > > > -----Message d'origine----- > > > How can I limit the result after the "order by" > > has > > > been executed? This stops efter 100 rows and the > > > result is not as I intended... > > > > > > Select * from mytable ORDER by total desc limit 0, > > 100 > > > > > > Must this be done in PHP? > > > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
