MySQL ORDER BY FIELD – Custom Field Sorting

Today I ran into a problem. I need to sort results of a given query by fixed column values.

Example

I have a column named status and which holds the following values 1,2,3,4

When I user normal sort method like

Order By status ASC will return 1,2,3,4

Order By status DESC will return 4,3,2,1

Suppose If I need the result to be sorted in a particular Order 3,2,4,1 then I ran into problem. Both method cannot solve my problem.

Then Googled and I found a thread in MySQL forum where Papalagi Pakeha had the same problem.

http://lists.mysql.com/mysql/209777

For that Johan Hââk replied with a solution

http://lists.mysql.com/mysql/209784

Where I can user Order By Field function to get the needed result. So my query will look something like

Order By Field(status,3,2,4,1) which does the magic. I searched the whole MySQL documentation but I am unable to find the proper documentation. Who cares it works for me πŸ˜‰

By Imthiaz

Programmer, SAAS, CMS & CRM framework designer, Love Linux & Apple products, Currently addicted to mobile development & working @bluebeetle

11 comments

  1. try this “select *,Field(status,3,2,4,1) from tbl Order By Field(status,3,2,4,1)” and you will see how it works πŸ™‚

  2. Nice.

    Example for string based custom ordering(for which I came to this article):
    ORDER BY FIELD(day, ‘Monday’, ‘Tuesday’, ‘Wednesday’, ‘Thursday’, ‘Friday’)

  3. This is very cool – you just saved me a lot of time doing my own custom pageinate method (CakePHP framework) πŸ™‚

  4. All I have to say is THANKS!!!! for the custom order sort…. with the FIELD technique….
    awesome!… thanks….

  5. Awesome options and THANKs.. this is a good option to sort the records in ascending or descending order and have the exceptional values that is those parameters in the field will be sorted last.
    Thanks to Copper and Puneet Pugalia and all the others

Comments are closed.