Re: Subject: Specific order by MySQL statement

At 09:37 08/11/2005, you wrote:

Message-ID: <436F3ED4.3080702@stvincent.ac.uk> Date: Mon, 07 Nov 2005 11:47:32 +0000 From: Adrian Bruce <abruce@stvincent.ac.uk> MIME-Version: 1.0 To: php-db@lists.php.net Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Subject: Specific order by MySQL statement Hi I am trying to get results from a MySQL query in a specific order, just using ASC or DESC will not work. For Example I have a field that can contain 4 possible values: 'Current', 'Completed','Withdrawn' or 'Transferred', I would like to order the results specifically like: Current Completed Withdrawn Transferred Is there any way i can do this using MySQL? Any help will be much appreciated.

Yes you can do this just fine. Make your transactiontype "field" an ENUM data type (i.e. a "list" of defined values). Add your ENUM elements in the order you want them to be retrieved (they're actually stored internally as bitwise mask values, i.e. as a number). Now, when you ORDER BY transactiontype, customerid , your results are ordered in the way the field was specified. So if you want this ordering, use ALTER TABLE bankdetails ADD transactiontype ENUM ('Current', 'Completed', 'Withdrawn', 'Transferred') Or to change the default ordering, ALTER TABLE bankdetails ADD transactiontype ENUM ('Current','Transferred', 'Completed', 'Withdrawn') If you do find there's a change needed to the ordering in future, probably the best way is to create a new column with the chosen ENUM ordering, then UPDATE bankdetails SET newfield=transactiontype , which makes sure MySQL re-maps the numeric representation correctly. HTH Cheers - Neil