It would help if you posted the actual query and results you are getting, instead of showing "simulated" results. You mention the query below in your first post, but you never give the actual results of it. Steve Meyers
> -----Original Message----- > From: Ashwin Kutty [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 18, 2001 5:58 AM > To: [EMAIL PROTECTED] > Subject: Re: MySQL 4.0 - Order By & Limit > > > > I just saw the mistake I made in my original post.. I want to Order By > letter, but when I do that with the Limit clause the results are as > shown in the original post.. Anyone know what the problem could be? > > > Steve Meyers wrote: > > >That is actually how it is supposed to work -- MySQL gets all > the rows, sorts them, and then returns up to the limit. What you > suggest would make no sense in any application, as far as I can > tell. The problem with the SQL queries given in the original > post is the the ordering is being done by #, not letter. Of > course it will be fairly haphazard. If you want to sort by > number, then letter, you will need to specify it in your query as > such (ORDER BY #, letter). > > > >Steve Meyers > > > > > >>-----Original Message----- > >>From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] > >>Sent: Tuesday, October 16, 2001 1:31 PM > >>To: [EMAIL PROTECTED] > >>Cc: [EMAIL PROTECTED] > >>Subject: Re: MySQL 4.0 - Order By & Limit > >> > >> > >>Hi, > >> > >>I don't see the interest of this kind of behaviour : the main benefit of > >>LIMIT is MySQL stops fetching rows as soon as the number of > rows specified > >>in LIMIT is achieved. > >>The behaviour you describe requires that MySQL fetches all the > rows, sorts > >>it, and then applies LIMIT. (so no speed optimization !). > >>For your example, just use the SQL query without LIMIT, and > then get only > >>the rows that interest you :) (I assume you are using language like PHP, > >>C++, etc... to execute this query ?) > >> > >>Regards, > >> > >>Jocelyn Fournier > >>Presence-PC > >> > >>----- Original Message ----- > >>From: "Ashwin Kutty" <[EMAIL PROTECTED]> > >>Cc: <[EMAIL PROTECTED]> > >>Sent: Tuesday, October 16, 2001 8:48 PM > >>Subject: MySQL 4.0 - Order By & Limit > >> > >> > >>>Hi, > >>> > >>>I thought version 4.0 was supposed to do a Order By first and then a > >>>Limit after that on the results returned on a SELECT.. I have just > >>>installed the new version and I hit the same problem I was hitting > >>>before, i.e., the Limit seems to be executing first and then the Order > >>>By causing all the results to be haphazard when they display.. Now if I > >>>go for a wider set of results, i.e. increase the Limit, the > results come > >>>a lot better then, i.e. more of them are ordered right, but > then, again, > >>>it still skips a lot more.. As an example:- > >>> > >>>Results Set > >>># letter > >>>1 f > >>>1 a > >>>2 b > >>>1 c > >>>3 d > >>>1 e > >>>1 h > >>>1 g > >>> > >>> > >>>Limit 2 > >>> > >>>Order By # > >>> > >>>1 a > >>>1 f > >>> > >>>Then when I go to the next set, I get > >>> > >>>1 c > >>>1 e > >>> > >>>Instead I would like it to be as the following:- > >>>First Set:- > >>>1 a > >>>1 c > >>>Second Set:- > >>>1 e > >>>1 f > >>> > >>>etc.. > >>> > >>>Any ideas? > >>> > >>>These are a few specs of the server & stuff.. > >>>Linux kernel 2.4.7-2 on a Redhat 8 server running Mysql 4.0.0-alpha > >>>The query I have is as follows:- > >>>SELECT * from tablename WHERE fieldname LIKE "%keyword%" ORDER BY > >>>fieldname DESC LIMIT 0,10; > >>> > >>>Thanks.. > >>> > >>> > >>>--------------------------------------------------------------------- > >>>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 > >> > >> > > > > > > > --------------------------------------------------------------------- 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
