How to Define A Custom ORDER BY in MySQL

In this tutorial, you'll learn how to custom order a MySQL table's data using the ORDER BY FIELD clause.

Code Example

To order a MySQL table by a custom order of the id column, you can use the ORDER BY FIELD clause.

Suppose you have a table named my_table with the following data:

idname
1John
2Jane
3Jeff
4Jenn

If you want to order the rows by the value in the id column in the following order: 4, 1, 3, 2, you can execute the following query:

SELECT * FROM my_table
ORDER BY FIELD(id, 4, 1, 3, 2);

The FIELD function accepts the id column as its first argument, then a list of values in the desired order as the subsequent arguments. The ORDER BY clause then sorts the rows based on the order of the id values in the list provided.

The request of the query would be:

idname
4Jenn
1John
3Jeff
2Jane

Conclusion

Here, you learned how to custom order a table's data in MySQL.

Last Updated: April 13, 2023
Created: February 17, 2023