Introduction

SQL does not guarantee the order of rows returned by a query unless you specify an ORDER BY clause. When you use an IN() clause to filter by a specific set of values, the results come back in whatever order the database engine finds most efficient, not in the order you listed the values. This article covers techniques across different database systems to force the result order to match the order of values in your IN() list.

Using FIELD() in MySQL

MySQL provides the FIELD() function, which returns the position of a value within a list of arguments. It returns 0 if the value is not found, and 1-based positions otherwise. This makes it straightforward to order results by the order of your IN() values.

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

The FIELD() function maps each id to its position in the provided list, so id = 5 gets position 1, id = 3 gets position 2, and so on. The ORDER BY then sorts by these positions. Note that you must repeat the value list in both the IN() and FIELD() clauses.

Using array_position() in PostgreSQL

PostgreSQL does not have a FIELD() function, but it provides array_position(), which returns the index of an element within an array.

1SELECT id, name
2FROM users
3WHERE id IN (5, 3, 8, 1, 9)
4ORDER BY array_position(ARRAY[5, 3, 8, 1, 9], id);

The ARRAY[5, 3, 8, 1, 9] literal creates a PostgreSQL array, and array_position() returns the 1-based index of each id within that array. This approach is clean and idiomatic for PostgreSQL.

Using CASE WHEN for Cross-Database Compatibility

The CASE WHEN expression works on all major SQL databases. You manually assign a sort position to each value.

1SELECT id, name
2FROM users
3WHERE id IN (5, 3, 8, 1, 9)
4ORDER BY CASE id
5    WHEN 5 THEN 1
6    WHEN 3 THEN 2
7    WHEN 8 THEN 3
8    WHEN 1 THEN 4
9    WHEN 9 THEN 5
10END;

This approach is verbose but universally supported. Each WHEN clause maps a value to its desired position. For long lists, the query becomes unwieldy, so consider generating it dynamically in your application code.

Using a VALUES List Joined as a Derived Table

You can create a derived table that pairs each value with its ordinal position, then join it to your main query. This keeps the ordering logic in a clean, separate structure.

1SELECT u.id, u.name
2FROM users u
3JOIN (
4    VALUES (5, 1), (3, 2), (8, 3), (1, 4), (9, 5)
5) AS ordering(id, sort_order) ON u.id = ordering.id
6ORDER BY ordering.sort_order;

This syntax works in PostgreSQL and SQL Server. In MySQL 8.0 and later, you can use a similar approach with a CTE.

1WITH ordering(id, sort_order) AS (
2    SELECT 5, 1 UNION ALL
3    SELECT 3, 2 UNION ALL
4    SELECT 8, 3 UNION ALL
5    SELECT 1, 4 UNION ALL
6    SELECT 9, 5
7)
8SELECT u.id, u.name
9FROM users u
10JOIN ordering o ON u.id = o.id
11ORDER BY o.sort_order;

This CTE approach works across MySQL, PostgreSQL, SQL Server, and SQLite, making it one of the most portable solutions.

Using FIND_IN_SET() in MySQL

MySQL also offers FIND_IN_SET(), which searches for a string within a comma-separated list and returns its position.

1SELECT id, name
2FROM users
3WHERE id IN (5, 3, 8, 1, 9)
4ORDER BY FIND_IN_SET(id, '5,3,8,1,9');

Note that FIND_IN_SET() operates on strings, so numeric values are implicitly cast. The comma-separated list must not contain spaces after the commas. This function is MySQL-specific and works similarly to FIELD(), but accepts the list as a single string argument.

Generating the ORDER BY Dynamically

In application code, you often build the IN() list from a programmatic array. You can generate the corresponding ORDER BY clause at the same time. Here is an example in Python.

1ids = [5, 3, 8, 1, 9]
2
3placeholders = ', '.join(['%s'] * len(ids))
4
5# For MySQL with FIELD()
6query = f"""
7    SELECT id, name
8    FROM users
9    WHERE id IN ({placeholders})
10    ORDER BY FIELD(id, {placeholders})
11"""
12params = ids + ids  # pass the list twice
13
14# For cross-database CASE WHEN
15case_clauses = ' '.join(
16    f'WHEN %s THEN {i}' for i in range(len(ids))
17)
18query = f"""
19    SELECT id, name
20    FROM users
21    WHERE id IN ({placeholders})
22    ORDER BY CASE id {case_clauses} END
23"""
24params = ids + ids

Generating the query dynamically avoids the tedium of writing long CASE expressions by hand and keeps your code maintainable when the value list changes.

Common Pitfalls

• Assuming IN() preserves order: The SQL standard does not guarantee any ordering unless ORDER BY is specified. Never rely on the IN() clause to implicitly order results.
• Forgetting to duplicate the value list: With FIELD() and array_position(), you must pass the values both in the IN() filter and in the ordering function. Mismatched lists produce incorrect sorting.
• Using FIND_IN_SET with spaces: FIND_IN_SET(id, '5, 3, 8') fails because the spaces become part of the search strings. Always use '5,3,8' with no spaces.
• Performance on large value lists: Functions like FIELD() and CASE WHEN with hundreds of values add overhead to the sort. For very large ordered lists, insert the values into a temporary table with a sort column and join against it.
• Mixing data types: FIND_IN_SET() and array_position() expect consistent types. Passing integers where strings are expected, or vice versa, can cause silent type coercion bugs or errors depending on the database.

Summary

• Use MySQL FIELD() for a concise, MySQL-native solution to order by IN() value position.
• Use PostgreSQL array_position() for the equivalent behavior in PostgreSQL.
• Use CASE WHEN for a portable solution that works on all SQL databases.
• Use a CTE or derived table with explicit sort positions for clean, readable queries that are easy to generate dynamically.
• Always include an explicit ORDER BY when result order matters, because SQL never guarantees row order without one.