Re: Subject: Specific order by MySQL statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux