permalink

11

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

11 Comments

  1. Nice.

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

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

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