For the best I know, it is not possible to do your task with single sql with MySQL. I have done similar task before but I need to put them into temp table and sort or create an index on the temp table.
As you can create temp table in memory (server), speed is really not a problem. You may find the 'CREATE TEMPORARY ....... TYPE=HEAP SELECT .... FROM' syntax very useful. If you are in some mean using presistant connection (like using PHP), be careful to DROP the temporary table after use. Regards, > I was hoping to do it in sql, since it would be faster. Any other ideas? > > --- > Rob > > ************************** > Rob Cherry > mailto:[EMAIL PROTECTED] > +27 21 447 7440 > Jam Warehouse RSA > Smart Business Innovation > http://www.jamwarehouse.com > ************************** > > -----Original Message----- > From: gerald_clark [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 18, 2003 11:36 PM > To: Rob > Cc: [EMAIL PROTECTED] > Subject: Re: Limits and order bys > > Sort them yourself after retrieving them. > > Rob wrote: > > >I have a question regarding the use of LIMIT with ORDER BY. My problem is > >as follows: > > > >I want my users to be able to pageanate through result sets, so I've > written > >some code > >that will display the results of a query 15 rows at a time in a HTML table. > >The next set of results > >can be accessed by pressing the next button and ditto for the previous set. > >The query is > > > >SELECT name FROM documents LIMIT 10, 15 > > > >I want to further extend this functionality by allowing the users to click > >on the column > >name (in the table header) and then sort the current set of results from > by > >the chosen > >column. So lets say I had the following > > > >mysql> SELECT name FROM documents LIMIT 2, 4; > > > >+-------------------------------------------+ > >| name | > >+-------------------------------------------+ > >| Visual Patterns Intellectual Property.doc | > >| footer.jpg | > >| getHWSerialNumber.txt | > >| env.php.txt | > >+-------------------------------------------+ > > > >Now, I want to order by name. So I should get > > > >+-------------------------------------------+ > >| name | > >+-------------------------------------------+ > >| env.php.txt | > >| footer.jpg | > >| getHWSerialNumber.txt | > >| Visual Patterns Intellectual Property.doc | > >+-------------------------------------------+ > > > >But, when I apply the following query > > > >mysql> SELECT name FROM documents ORDER BY name ASC LIMIT 2, 4; > > > >I get > > > >+-----------------------+ > >| name | > >+-----------------------+ > >| env.php.txt | > >| footer.jpg | > >| getHWSerialNumber.txt | > >| Ideas.doc | > >+-----------------------+ > > > >I can only assume that MySql is ordering by name before limiting the result > >set. Obviously > >I want the reverse. Any ideas? > > > >Thanks > > > >--- > >Rob > > > > > > > > > > --------------------------------------------------------------------- > 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 <mysql-unsubscribe- [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Yours, KH Chiu C&A Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.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
