How to Define a Custom Sorting Order in MySQL Using FIELD()?

Ordering Data with Custom Order in MySQL

In MySQL, defining a custom sorting order is possible with the FIELD() function. This function evaluates a field's value against a list of values and returns a numerical value based on the position of the value in the list. By leveraging this function, you can specify the desired order for specific values.

Consider the following table:

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

To return the rows sorted by Language and ascending ID, with ENU being the first priority, followed by JPN, and then DAN, you can use the following ORDER BY clause:

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

This query instructs MySQL to sort the results by the Language field, using the values 'ENU', 'JPN', and 'DAN' as the custom sorting order. Rows with the value 'ENU' in the Language field will appear first, followed by those with 'JPN', and finally those with 'DAN'. Within each Language group, rows will be ordered by ascending ID.

It's important to note that:

• The FIELD() function may limit the portability of your SQL, as other DBMSs may not support it.
• For larger lists of values to sort by, it's generally more efficient to create a separate table with a sortorder column and join it to your queries for ordering.

The above is the detailed content of How to Define a Custom Sorting Order in MySQL Using FIELD()?. For more information, please follow other related articles on the PHP Chinese website!