SQLite Forum

Is the order conserved in a table from a VALUES clause?
> Second, if you have your Primary Key match the internal 
> SQLite rowid, as you do, and explicitly provide those PKs 
> are you do in your example, then simply use select val, txt 
> from x ORDER BY val to have a deterministic order for free, 
> since the FULL SCAN will be in PK-order, no need for Window 
> functions.

It's not the ORDER BY of the x table that I'm worried about. 

The ORDER BY that I am concerned with is the one in the table produced by the VALUES clause - i.e. (5), (7), (1) - and then I want the values selected from the x table to be in the order of 5,7,1 - i.e. as defined by the VALUES clause.

> SQLite will never provide any guarantees for what order 
> rows are stored into, nor how they are selected into (w/o 
> an order by clause), whether you insert them with values() 
> or not. How it works now is not relevant either, as that 
> could change too.

So, you are saying, that given the current implementation of SQLite, the
only way I can *_guarantee_* the order by in the VALUES clause is
to do something like ([fiddle](https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=a8d7d75287ffc0682be59eea53a5f475)):

  st.id, st.filler, tab.rn, tab.column1, tab.column2
FROM some_table st
    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;

Though this *always* seems to work (same fiddle):

SELECT ROW_NUMBER() OVER () AS rn, * FROM (VALUES (5), (7), (1));

gives (1, 5), (2, 7)... &c. - i.e. order is respected.

I don't see why respecting the ordering of the input sequence in the VALUES clause couldn't be made the default - to me it makes perfect sense. Any implementations that explicitly use the ORDER BY will continue to function... any that don't - behaviour was undefined anyway!

Anway, it would be nice to have a WITH ORDINALITY clause to imitate PostgreSQL's functionality - as I said, why put the VALUEs clause in a certain order if you didn't want that order in the first place?

Thanks for your input on this! Rgs,