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 >> >> > >
