MySQL’s FIELD() function helps you sort query results in a custom order, so records appear according to your priorities rather than their default ENUM order. See how FIELD() works with ORDER BY to arrange values from most to least urgent, and explore the function documentation at https://dev.mysql.com/doc/refman/9.7/en/string-functions.html#function_field
Learn how to create a custom sort order in MySQL using the FIELD() function with the ORDER BY clause. When sorting query results, the default order of values may not match the sequence you need. MySQL’s FIELD() function provides a way to specify the order in which values should appear, making it useful when sorting records by custom priorities instead of their predefined order.
This demo uses a ticket table with a priority column defined as an ENUM containing four values: coffee emergency, house on fire, mild inconvenience, and slight annoyance. By default, sorting the priority column returns values in the order specified in the ENUM definition, placing coffee emergencies ahead of fires. The example illustrates why the default sort order may not reflect the intended level of urgency.
The demo shows how to use FIELD() to establish a different sorting sequence. The function takes the column being sorted as its first argument, followed by the values in the desired order. FIELD() returns the position of each matching value, and ORDER BY uses those numeric positions to sort the query results.
See how combining MySQL FIELD() with ORDER BY makes it possible to organize tickets from most to least urgent. This approach helps you control the sequence of query results when a predefined value order doesn’t match the priorities you want to apply.
Link to code used in demo: https://github.com/mysql/mysql-community/tree/main/content/mysql_shorts/Episode%20104%20-%20field()
