Ordering by the order of values in a SQL IN() clause

sql

sorting

best-practices

joins

byAnton Shumikhin·Aug 19, 2024

In MySQL, apply this pattern to sort based on a custom sequence using ORDER BY FIELD:

SELECT * FROM mytable 
WHERE id IN (3, 6, 1, 7) 
ORDER BY FIELD(id, 3, 6, 1, 7); -- Remember, Mr. Bean (id) goes to places (3,6,1,7) in order!

In databases that don't support FIELD, use CASE in the ORDER BY clause:

SELECT * FROM mytable 
ORDER BY CASE id
           WHEN 3 THEN 1 -- WHEN, not WEN or THEN, but WHEN3THEN1...it's like, Star Wars episode number 😉
           WHEN 6 THEN 2
           WHEN 1 THEN 3
           WHEN 7 THEN 4
         END;

Maneuvering databases without FIELD()

Not all SQL dialects incorporate FIELD(). For instance, Oracle promotes the instr() function for achieving a similar effect:

SELECT * FROM mytable 
WHERE id IN (3, 6, 1, 7)
ORDER BY instr(',3,6,1,7,', ',' || id || ','); -- Don't get lost in the forest, use instr!

instr() function locates the position of a substring within a string, also building that desirable order by finding the ids' positions in the sequence.

For more compatibility with ANSI SQL and refined sorting scenarios, consider the VALUES utility to create a temp table. You can then accompany that table:

WITH OrderedValues (id, order_seq) AS (
  VALUES (3, 1), (6, 2), (1, 3), (7, 4) -- Hey look, a map! 🗺️ 3->1, 6->2, etc.
)
SELECT m.* FROM mytable m
JOIN OrderedValues o ON m.id = o.id
ORDER BY o.order_seq; -- Follow the order_seq like it's treasure map!

Mastering advance sorting techniques

How to sort large amounts of data

Sorting can pose challenges with huge datasets. To manage processing efficiently:

Embrace a secondary column to solve tiebreakers and avoid arbitrary orderings.
An indexed column for sorting significantly cuts down query times.
Avert overly fancy CASE statements that could become performance snares.

SQL patterns: Common pitfalls to dodge

Stay aware of database-specific behaviors when manufacturing custom sort ordering:

Shun the melding of user-based and automatic backend queries, which can lead to locking and concurrency dilemmas.
Account for hidden costs like full table scans or sort operations which may come along with certain SQL patterns.

Considering universality and portability

Best practices for cross-database scenarios

For ensuring compatibility across varied SQL platforms:

ORDER BY CASE: An oldie but a goodie, it works almost everywhere.
ORDER BY instr(): An alternative to FIELD() that’s also pretty flexible.
Temporary tables: Creating momentary data structures for the explicit task of sorting. It’s a universal approach that's well-supported.

Joining for perfect sequence

When more control is required, use joins:

A LEFT JOIN against a values list retains the order while including all left-hand side records.
Make sure your join doesn't upset the set order; pick columns wisely.

Navigating adaptability and limitations

Understanding FIELD()'s limitations

While FIELD() is compact, it’s MySQL-specific. It's not part of SQL standards and is unavailable in many other SQL databases.

Sorting minus subqueries

There are times when embedding subqueries to sort isn't feasible. In such situations:

ORDER BY instr() gives a cleaner approach in MySQL for matching order lists.
Remember, precision is the name of the game in constructing the listing string to ensure accurate sort order.

explain-codes / Sql / Ordering by the order of values in a SQL IN() clause

Linked

Order BY the IN value list



Alternative to column name "order" in MySQL



Sql order string as number



Postgresql GROUP_CONCAT equivalent?



Sql - using alias in Group By

