SQLite Forum

Is the order conserved in a table from a VALUES clause?
Login
This:

WITH ov(idx,val) AS (SELECT * FROM (VALUES (1,'Z'),(3,'M'),(2,'A')) ORDER BY 1) SELECT * FROM ov;
idx  val
---  ---
1    Z
2    A
3    M

still uses a coroutine, but returns the entries in the requested order. And is future proof, because it explicitly specifies an order.

explain with ov(idx,val) as (select * from (values (1,'Z'),(3,'M'),(2,'A')) order by 1) select * from ov;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  NULL
1     InitCoroutine  1     12    2                    00  NULL
2     Integer        1     2     0                    00  NULL
3     String8        0     3     0     Z              00  NULL
4     Yield          1     0     0                    00  NULL
5     Integer        3     2     0                    00  NULL
6     String8        0     3     0     M              00  NULL
7     Yield          1     0     0                    00  NULL
8     Integer        2     2     0                    00  NULL
9     String8        0     3     0     A              00  NULL
10    Yield          1     0     0                    00  NULL
11    EndCoroutine   1     0     0                    00  NULL
12    SorterOpen     2     4     0     k(1,B)         00  NULL
13    InitCoroutine  1     0     2                    00  NULL
14      Yield          1     21    0                    00  NULL
15      Copy           2     7     0                    00  NULL
16      Copy           3     8     0                    00  NULL
17      Copy           7     6     0                    00  NULL
18      MakeRecord     6     3     9                    00  NULL
19      SorterInsert   2     9     6     3              00  NULL
20    Goto           0     14    0                    00  NULL
21    OpenPseudo     3     10    4                    00  NULL
22    SorterSort     2     28    0                    00  NULL
23      SorterData     2     10    3                    00  NULL
24      Column         3     2     8                    00  NULL
25      Column         3     1     7                    00  NULL
26      ResultRow      7     2     0                    00  NULL
27    SorterNext     2     23    0                    00  NULL
28    Halt           0     0     0                    00  NULL