Re: Subject: Specific order by MySQL statement

Re: Subject: Specific order by MySQL statement

am 08.11.2005 12:24:15 von Neil Smth

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php