At 09:37 08/11/2005, you wrote:
Message-ID: <436F3ED4.3080702@xxxxxxxxxxxxxxx>
Date: Mon, 07 Nov 2005 11:47:32 +0000
From: Adrian Bruce <abruce@xxxxxxxxxxxxxxx>
MIME-Version: 1.0
To: php-db@xxxxxxxxxxxxx
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
