1

Is it possible to return rows in the same order as passed in the IN clause, e.g

SELECT * FROM some_table WHERE id IN (5, 7, 1);

and the rows are returned as:

id |     value
--------------------
 5 | something
 7 | something else
 1 | some value

The closest example I found was the Postgres WITH ORDINALITY.

1
  • As of now, I'm informed that (SQLite lists), as @DavidSpillett pointed out in his comment to my answer, the order of the data in the VALUES clause is not guaranteed to be propagated in any further queries/manipulations. I have put in a method which will guarantee order, but it involves explicitly specifying the order on creation of the table derived from the VALUES clause!
    – Vérace
    Nov 2, 2021 at 13:09

2 Answers 2

2

You can do this as follows - or see the revised method below, probably better (initial code available on fiddle here - revised fiddle here, and a third method here):

Initial method:

CREATE TABLE some_table
(
  id INT NOT NULL PRIMARY KEY,
  filler TEXT NOT NULL
);

Populate:

INSERT INTO some_table VALUES 
(1,   '1_val'),
(2,   '2_val'),
(3,   '3_val'),
(4,   '4_val'),
(5,   '5_val'),
(6,   '6_val'),
(7,   '7_val'),
(8,   '8_val'),
(9,   '9_val'),
(10, '10_val');

and then run the following SQL:

SELECT 
  st.id, st.filler
FROM some_table st 
WHERE id IN (5, 7, 1)
ORDER BY 
  CASE st.id
    WHEN 5 THEN 1
    WHEN 7 THEN 2
    WHEN 1 THEN 3
  END ;

Result:

id  filler
5   5_val
7   7_val
1   1_val

Revised method with ORDER (current versions):

You can also do the following - possibly better if you have a long list of values?

SELECT
  st.id, st.filler, tab.rn, tab.column1
FROM some_table st
JOIN
(
  SELECT 
    ROW_NUMBER() OVER () AS rn, column1 
    FROM (VALUES (5), (7), (1))
) AS tab
ON st.id = tab.column1
ORDER BY tab.rn;

Result:

id  filler  rn  column1
 5   5_val   1        5
 7   7_val   2        7
 1   1_val   3        1

You can, of course, not select the rn and column1 columns to get your desired result - see revised fiddle!

From here, unfortunately SQLite doesn't allow you to alias the table constructed from the VALUES clause (see here) - PostgreSQL does allow this - see here.

NOTE!

A discussion on the SQLite mailing lists revealed that this is undocumented and cannot be relied on into the future - but is the behaviour for all versions up to the current one (3.36). I suggested that the behaviour be retained and documented - no reply forthcoming so far - becomes technically complex...

Guarantee ORDER (now and into the future)

Having posted on SQLite lists, the current state of play appears to be that as @DavidSpillett said, the order in the VALUES clause is not guaranteed to be propagated down to any SELECT on any table resulting from the VALUES clause - into the future. At the time of writing, (Nov 3rd, 2021), the order of the VALUES clause will be respected (up to version 3.36) but this is not guaranteed into the future.

So, you'll have to explicitly put an order in with your data as follows (see fiddle):

SELECT
  st.id, st.filler, tab.rn, tab.column1, tab.column2
FROM some_table st
JOIN
(
  SELECT 
    ROW_NUMBER() OVER () AS rn, x.column1, x.column2 
    FROM (VALUES (1, 5), (2, 7), (3, 1)) AS x
) AS tab
ON st.id = tab.column2
ORDER BY tab.column1;

Result (leave out unwanted fields):

id   filler     rn  column1     column2
 5    5_val      1        1           5
 7    7_val      2        2           7
 1    1_val      3        3           1

Another guaranteed order method

I found this neat little "trick" here (see the fiddle here). It's what I call a "poor man's recursion"...

SELECT 
  id,
  filler, 
  INSTR(',5,7,1,)', ',' || id || ',') AS stuff
FROM some_table
WHERE stuff > 0
ORDER BY stuff;

Result - same as for the others.

The commas before the field value mean that, say, 111 isn't picked up when you're searching for 11 - SQLite performs an implicit CAST - but that should not be relied upon. PostgreSQL does the same thing (see the fiddle here), i.e. CASTing isn't required. In this latter fiddle, I've gone through the steps one by one - helped get it clear in my own mind!

4
  • I doubt that the rownumber() method is guaranteed to work. It may always work now in small , but change with later versions, or just in larger or more complex examples. The data in an IN clause is a set and has no implicit ordering so unless there are explicit hints you can give to force fixed ordering you should assume the order is not fixed (just like select with no order by). Nov 2, 2021 at 9:30
  • @DavidSpillett - checked on SQLite list - have been told that there is, as you pointed out (thanks again), no guarantee that the order in the VALUES clause will be respected down the line without this being explicitly specified. I've added a fiddle that will guarantee this by adding the order explicitly. A pity that SQLite doesn't have PostgreSQL's WITH ORDINALITY functionality!
    – Vérace
    Nov 2, 2021 at 13:05
  • @DavidSpillett - I referrd to the SQLite forum here - the order of the VALUES clause is respected up to the current version, but this is not documented and not guaranteed to be SQLite's behaviour going forward - so caveat emptor. I suggested that it should be documented and kept as the default! Your comment really made me think about all of this, so thanks for the learning experience! :-)
    – Vérace
    Nov 3, 2021 at 10:38
  • As well as undefined/undocumented behaviours not being safe to rely on between versions of a given DB, they can be a source of tricky to diagnose bugs if you ever need to more between current versions of different DBs. If you use something like this, leave a comment for others (or future you!) to the effect that the code relies on an undocumented behaviour but works well now (perhaps include a link to those references above, so you don't have to redo the research later). Nov 3, 2021 at 21:05
3

Assuming the json1 extension is available and enabled you could use the json_each function to join on a JSON array containing the IDs.

json_each is a table-valued function that returns a row for each value of a JSON array (or object).

The rows returned by json_each have the schema

CREATE TABLE json_each(
    key ANY,             -- key for current element relative to its parent
    value ANY,           -- value for the current element
    type TEXT,           -- 'object','array','string','integer', etc.
    atom ANY,            -- value for primitive types, null for array & object
    id INTEGER,          -- integer ID for this element
    parent INTEGER,      -- integer ID for the parent of this element
    fullkey TEXT,        -- full path describing the current element
    path TEXT,           -- path to the container of the current row
    json JSON HIDDEN,    -- 1st input parameter: the raw JSON
    root TEXT HIDDEN     -- 2nd input parameter: the PATH at which to start
);

In this case only the key and value columns are needed.

The value column contains the value for each item in the array and the key column contains the zero-based index of the value.

So given the JSON array [5, 7, 1] calling json_each('[5, 7, 1]') would return

key  value
---  -----
0    5    
1    7    
2    1  

Note that while the documentation does not guarantee the order of the rows returned by json_each the key will always contain the correct index and can thus be used for ordering.

So using json_each you could write your query like this:

SELECT 
    st.id, st.filler
FROM some_table st
JOIN json_each('[5,7,1]') AS tab
WHERE st.id = tab.value
ORDER BY tab.key

fiddle

While this solution does require the json1 extension the extension should be available and enabled by default in most environments.

More importantly this solution does not require manually generating a VALUES list. Instead the JSON can simply be a parameter that can be bound to a prepared statement.

The only other requirement other than the json1 extension is some kind of tool or library to generate a JSON array from a list, but most environments should already provide either one.

And if not, assuming all values are guaranteed to be numbers, the array can easily be generated using normal string concatenation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.