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:
id | name |
---|---|
1 | John |
2 | Jane |
3 | Jeff |
4 | Jenn |
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:
id | name |
---|---|
4 | Jenn |
1 | John |
3 | Jeff |
2 | Jane |
Conclusion
Here, you learned how to custom order a table's data in MySQL.
Last Updated: April 13, 2023
Created: February 17, 2023
Created: February 17, 2023