At 09:37 08/11/2005, you wrote:
Date: Mon, 07 Nov 2005 11:47:32 +0000
From: Adrian Bruce <[EMAIL PROTECTED]>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Specific order by MySQL statement


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:


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.

Cheers - Neil

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to