Introduction

When you need rows in a custom ID order such as 5, 9, 2, 14, a normal ORDER BY id is not enough. In MySQL, the most direct solution is usually FIELD(id, ...), which assigns each listed ID a sort position. For larger or more dynamic lists, a join against an ordering table or derived table is often cleaner.

The Simple MySQL Solution: FIELD()

FIELD() returns the 1-based position of a value within a list. That makes it a natural fit for custom sorting.

1SELECT id, name
2FROM products
3WHERE id IN (5, 9, 2, 14)
4ORDER BY FIELD(id, 5, 9, 2, 14);

This produces rows in exactly that order:

1. 5
2. 9
3. 2
4. 14

For one-off queries, this is usually the clearest answer.

Why FIELD() Works

Conceptually, MySQL evaluates something like this behind the scenes:

• 'FIELD(id, 5, 9, 2, 14) returns 1 when id = 5'
• it returns 2 when id = 9
• it returns 3 when id = 2
• it returns 4 when id = 14

Then ORDER BY sorts by that computed position.

If an ID is not in the list, FIELD() returns 0, which usually sorts before listed values unless you handle it explicitly.

Handling Rows Not In The Preferred List

Suppose you want listed IDs first and everything else after them. One approach is to sort by whether the value appears in the list and then by the FIELD() position.

1SELECT id, name
2FROM products
3ORDER BY
4    FIELD(id, 5, 9, 2, 14) = 0,
5    FIELD(id, 5, 9, 2, 14),
6    id;

This gives you:

• listed IDs in your chosen order
• unlisted IDs afterward
• a stable fallback ordering by id

That pattern is useful when the query is broader than just a WHERE id IN (...) filter.

CASE Is A Portable Alternative

If you want SQL that is easier to move across database engines, use CASE instead of FIELD().

1SELECT id, name
2FROM products
3ORDER BY CASE id
4    WHEN 5 THEN 1
5    WHEN 9 THEN 2
6    WHEN 2 THEN 3
7    WHEN 14 THEN 4
8    ELSE 999
9END;

This is more verbose than FIELD(), but it is standard SQL style and works well when portability matters more than brevity.

Better For Larger Dynamic Lists: Join An Ordering Table

When the custom order comes from application input or another table, hardcoding many values inside FIELD() becomes awkward. In that case, join to a table or derived table that stores the desired order.

1SELECT p.id, p.name
2FROM products AS p
3JOIN (
4    SELECT 5 AS id, 1 AS sort_order
5    UNION ALL SELECT 9, 2
6    UNION ALL SELECT 2, 3
7    UNION ALL SELECT 14, 4
8) AS desired_order
9    ON p.id = desired_order.id
10ORDER BY desired_order.sort_order;

This is easier to generate programmatically and easier to extend if the ordering metadata already exists in the database.

For truly persistent custom ordering, storing sort_order in a real table is usually better than building the list inside every query.

Performance Considerations

For a small list, FIELD() is usually fine. For a large list of IDs, performance and maintainability can suffer because:

• the SQL string grows large
• the expression becomes harder to read
• query planning may be less friendly than a join-based design

If the order list is large or reused often, put it in a table and join on it.

Also note that a custom sort expression generally means MySQL cannot simply use the natural ordering of an index for the final output. That is expected. The point of the query is custom order, not raw indexed order.

Choosing The Right Pattern

Use this rule of thumb:

• short fixed list: FIELD()
• portable SQL across engines: CASE
• long or dynamic list: join to ordering data

That keeps the query aligned with the real source of truth for the ordering logic.

Common Pitfalls

• Using ORDER BY id and expecting it to respect the input sequence.
• Forgetting that FIELD() returns 0 for values not in the list.
• Repeating a long FIELD() expression in many places instead of storing order metadata once.
• Choosing FIELD() when database portability is a hard requirement.
• Passing a very large dynamic list directly into SQL without considering query size and maintainability.

Summary

• In MySQL, ORDER BY FIELD(id, ...) is the simplest way to sort by a specific ID sequence.
• 'CASE provides a more portable but more verbose alternative.'
• For large or dynamic order lists, join to a table or derived table with explicit sort positions.
• Be explicit about what should happen to rows not included in the preferred ID list.
• Pick the approach based on query size, portability needs, and where the order data comes from.