How to ORDER BY the Order of Values in a SQL IN() Clause

1. Overview

The ORDER BY clause in SQL sorts the result set of a SELECT statement by specified columns in ascending order by default. It can be difficult to organize SQL query results depending on a particular set of variables, particularly when the intended order isn’t inherent to the data.

In this tutorial, we’ll explore various methods of ordering the specified values in SQL results using the Baeldung University schema.

2. Using a CASE Statement

The SQL CASE statement goes through each condition and will return the value that matches the first condition. It’s a versatile and universally supported SQL tool that works with all the major databases, including MySQL, PostgreSQL, SQL Server, and SQLite. Additionally, it is an effective SQL tool for designing unique sorting logic.

By explicitly defining the order within the CASE statement, we can control the order of the results. For illustration, consider the following query:

SELECT id, name
FROM Student
WHERE id IN (1110, 1101, 1617, 1107)
ORDER BY CASE
    WHEN id = 1110 THEN 1
    WHEN id = 1101 THEN 2
    WHEN id = 1617 THEN 3
    WHEN id = 1107 THEN 4
    ELSE 5
END;

In the above query, the CASE statement checks id of each student and assigns it a numerical value based on the desired order. The ORDER BY clause uses these custom numerical values to sort the results.

3. Using the FIELD Function

The FIELD function is MySQL-specific. It returns a value’s index position within a list of values. To demonstrate, let’s look at the ORDER BY clause with FIELD:

SELECT id, name
FROM Student
WHERE id IN (1110, 1101, 1617, 1107)
ORDER BY FIELD (id, 1110, 1101, 1617, 1107);

The FIELD function returns the position of id within the list (1110, 1101, 1617, 1107). The ORDER BY clause sorts the results based on these positions. If we miss an id from the FIELD function, it will return zero, which automatically positions that value at the beginning of the sorted result.

4. Using a Common Table Expression (CTE)

A Common Table Expression (CTE) provides a way to create a temporary result set to reduce the complexity and enhance the readability of a SQL query. Using CTEs, complex queries can be broken down into simpler, more manageable sections and referred to more than once in the same query.

To get the desired order, we’ll first create the OrderList CTE which explicitly defines the custom order of the values by assigning a unique order value, ord. This OrderList CTE acts as a reference table that outlines how we want the IDs to be sorted. Then we join this CTE with the main table for custom ordering with the ord column.

With the CTE and main table joined, we ensure that the final result set is sorted exactly as specified:

WITH OrderList AS (
    SELECT 1110 AS id, 1 AS ord
    UNION ALL
    SELECT 1101 AS id, 2 AS ord
    UNION ALL
    SELECT 1617 AS id, 3 AS ord
    UNION ALL
    SELECT 1107 AS id, 4 AS ord
)
SELECT s.id, s.name
FROM Student s
JOIN OrderList ol ON s.id = ol.id
ORDER BY ol.ord;

The WITH OrderList AS () expression in the query above defines a CTE called OrderList to declare our expected order. Additionally, the main SELECT statement joins the OrderList CTE with the Student table on the id column. Lastly, the ORDER BY clause uses the ord column from the CTE to sort the results.

5. Using a Temporary Table

A temporary table is an intermediate table created on the fly during the runtime. These tables can handle many types of SQL operations and are removed after the client connection closes.

We can use the temporary table to order our values by creating a temporary table that explicitly defines the desired order of the values. The main query then joins this temporary table with the original table and sorts the results based on the order specified in the temporary table:

CREATE TEMPORARY TABLE temp_order (id INT, ord INT);
INSERT INTO temp_order (id, ord) VALUES (1110, 1), (1101, 2), (1617, 3), (1107, 4);

SELECT s.id, s.name 
FROM Student s
JOIN temp_order t ON s.id = t.id
ORDER BY t.ord;

This SQL script will create a temporary table temp_order with columns id and ord. Further, it inserts specific values into temp_order to associate a custom order number for each student ID with the ord column.

The subsequent query retrieves student IDs and names from the Student table, joining with the temp_order by id. Specifically, the ord column of temp_order will sort the final result.

6. Conclusion

In this article, we explored various methods to ORDER BY the order of custom values in a SQL IN() clause. First, we looked at the CASE and FIELD functions to fetch the required data in a particular order. Then, we reviewed a common table expression and a temporary table as additional strategies to fetch the data in a particular order.