Maintain order when using SQLite WHERE-clause and IN operator

Question

Consider the following tbl:

CREATE TABLE tbl (ID INTEGER, ticker TEXT, desc TEXT);

INSERT INTO tbl (ID, ticker, desc) 
    VALUES (1, 'GDBR30', '30YR'),
           (2, 'GDBR10', '10YR'),
           (3, 'GDBR5', '5YR'),
           (4, 'GDBR2', '2YR');

For reference, tbl looks like this:

ID   ticker   desc
1    GDBR30   30YR
2    GDBR10   10YR
3    GDBR5    5YR
4    GDBR2    2YR

When issuing the following statement, the result will be ordered according to ID.

SELECT * FROM tbl
WHERE ticker in ('GDBR10', 'GDBR5', 'GDBR30')

ID   ticker   desc
1    GDBR30   30YR
2    GDBR10   10YR
3    GDBR5    5YR

However, I need the ordering to adhere to the order of the passed list of values. Here's what I am looking for:

ID   ticker   desc
2    GDBR10   10YR
3    GDBR5    5YR
1    GDBR30   30YR

Always include ORDER BY clause in your query when ordering the result is of importance for a consumer. Never rely on the other parts of the query. — Serg, May 05 '21 at 07:19
"When issuing the following statement, the result will be ordered according to ID". No, it is ordered in whatever way the engine gets the data. Without an `ORDER BY` there is no guarantee about the sorting. — HoneyBadger, May 05 '21 at 07:20

score 1 · Answer 1 · answered May 05 '21 at 07:43

The only way to be sure of the final ordering of records is to use the ORDER BY clause.

The order the list of values is given is not relevant for final ordering.

In your case your only solution is to give to each value a 'weight' to use as sort order.

You could for example change the IN operator with a INSTR function to get both a filterable and a sortable result.

Try something like that

SELECT *, INSTR(',GDBR10,GDBR5,GDBR30,', ',' || ticker || ',') POS
FROM tbl
WHERE POS>0
ORDER BY POS;

If you don't want the position in the selected fields list you can use a subquery:

SELECT * 
FROM (SELECT *, INSTR(',GDBR10,GDBR5,GDBR30,', ',' || ticker || ',') pos FROM tbl) X
WHERE  POS>0
ORDER BY POS;

Almost like a RECURSIVE query within the `INSTR` function - really nice (+1)! — Vérace, Nov 03 '21 at 08:48

score 1 · Accepted Answer · answered May 05 '21 at 07:50

You can create a CTE that returns 2 columns: the values that you search for and for each value the sort order and join it to the table.
In the ORDER BY clause use the sort order column to sort the results:

WITH cte(id, ticker) AS (VALUES (1, 'GDBR10'), (2, 'GDBR5'), (3, 'GDBR30'))
SELECT t.* 
FROM tbl t INNER JOIN cte c
ON c.ticker = t.ticker
ORDER BY c.id

See the demo.

Maintain order when using SQLite WHERE-clause and IN operator

2 Answers2

Linked