Search code examples
mysqlsortingsql-order-by

How to define a custom ORDER BY order in mySQL

In MySQL how do I define a custom sorting order.

To try to explain what I want consider this table:

ID  Language    Text
0   ENU         a
0   JPN         b
0   DAN         c       
1   ENU         d
1   JPN         e
1   DAN         f
2   etc...

here I want to return all rows sorted by Language and ascending ID so that Language = ENU comes first, then JPN and lastly DAN.

The result should be: a,d,b,e,c,f etc.

Is this even possible?

Solution

  • MySQL has a handy function called FIELD() which is excellent for tasks like this.

    ORDER BY FIELD(Language,'ENU','JPN','DAN'), ID

    Note however, that

    1. It makes your SQL less portable, as other DBMSs might not have such function

    2. When your list of languages (or other values to sort by) gets much longer, it's better to have a separate table with sortorder column for them, and join it to your queries for ordering.