> 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)):
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;
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,