MySQL - ORDER BY values within IN
System Design practice on Codemia
Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.
Introduction
When you filter rows with IN, MySQL does not automatically return them in the same order as the values in that list. If you need custom ordering that matches the input sequence, you must express that ordering explicitly. For small fixed lists, FIELD() is usually the simplest solution. For larger or generated lists, CASE or a join against an ordering table is often easier to maintain.
Why IN Does Not Preserve Your Input Order
This query filters correctly:
But SQL does not promise rows will come back as 7, 3, 10, 2 unless you add an ORDER BY. Without one, MySQL can return rows in whatever order the execution plan produces.
That is why the ordering logic must be separate from the filtering logic.
The Simple Solution: FIELD()
For a small fixed list, FIELD() is the usual MySQL-specific answer:
FIELD(id, 7, 3, 10, 2) returns 1 for 7, 2 for 3, 3 for 10, and 4 for 2. MySQL then sorts by those positions.
This is concise and works well when the list is known and not too large.
A Portable Alternative: CASE
If you want more control or prefer something closer to standard SQL, use CASE:
This is more verbose, but it is explicit and easier to adapt when your ordering logic is more complex than a simple value list.
When the List Comes From Application Code
In real applications, the list often arrives dynamically from Node.js, Python, PHP, or another backend. In that case:
- generate the
INlist safely with parameter binding - generate the same order definition for
FIELD()orCASE - do not build raw SQL through string concatenation with untrusted input
The query pattern is fine. The dangerous part is usually how the SQL string is assembled in the application.
Better for Larger Lists: Join Against an Order Map
If the ordering list becomes large or reused often, a derived table or temporary table is clearer than a giant FIELD() call.
This scales better conceptually and is often easier to generate programmatically when the list is long.
Handling Missing or Extra Values
Be clear about what should happen if:
- the
INlist references values not present in the table - the query returns rows not covered by your custom order expression
With CASE, an ELSE branch makes the behavior explicit. With FIELD(), unmatched values sort as 0, which can surprise people if the query changes later.
That is one reason CASE can be safer in evolving SQL.
Common Pitfalls
- Assuming
INpreserves the input order by itself. - Using
FIELD()with application-generated values but building the SQL unsafely. - Forgetting to keep the
INlist and the custom ordering list in sync. - Creating huge
FIELD()orCASEexpressions when a join table would be cleaner. - Ignoring how unmatched values will sort in future query changes.
Summary
- '
INfilters rows, but it does not define a custom output order.' - Use
ORDER BY FIELD(...)for small MySQL-specific ordered lists. - Use
CASEwhen you want explicit or more portable ordering logic. - For larger dynamic lists, joining against an order map is often cleaner.
- Keep filtering and ordering logic aligned so the query stays deterministic.
Related reading
- MySQL - SELECT WHERE field IN subquery - Extremely slow why?
- MySQL - This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery
- MySQL - UPDATE query based on SELECT Query
- MySQL - Using COUNT in the WHERE clause
- MySQL 'Order By' - sorting alphanumeric correctly
- MySql order by specific ID values
- MySQL - why not index every field?
- MySQL 5.0 indexes - Unique vs Non Unique

System Design Fundamentals
Build a strong foundation in designing scalable, reliable distributed systems.
View the courseTrack what you have practised
A free account saves your progress, solutions and study plan across every problem on Codemia.
System Design practice on Codemia
Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.