Hi all,
I'm looking for suggestions...
What I want to achieve is to "roll" the result set of a query around by
a certain amount (as in offset + wrap around).
For example, given that:
CREATE TABLE t (id TEXT NOT NULL PRIMARY KEY);
INSERT INTO t VALUES ('pen'), ('tree'), ('desk'), ('car');
I would like to have the same result set of:
SELECT * FROM t ORDER BY id ASC;
car
desk
pen
tree
Except I would like it to start at "pen", producing:
pen
tree
car
desk
Now, one way to do it is:
SELECT * FROM t ORDER BY CASE WHEN id >= 'pen' THEN 0 ELSE 1 END, id;
Except that this pretty much kills the query efficiency:
0|0|0|SCAN TABLE t
0|0|0|USE TEMP B-TREE FOR ORDER BY
As opposed to the original query whose query plan is:
0|0|0|SCAN TABLE t USING COVERING INDEX sqlite_autoindex_t_1
Since fiddling with ORDER BY seems to always kill the index scan
optimization, I've instead resorted to split the query and use WHERE.
The following happens to work and to properly make use of indexes:
SELECT * FROM (SELECT * FROM t WHERE id >= 'pen' ORDER BY id)
UNION ALL
SELECT * FROM (SELECT * FROM t WHERE id < 'pen' ORDER BY id);
However, looking at the docs I couldn't find any guarantee that "UNION
ALL" preserves the inner ordering.
Can somebody confirm that the ordering is always preserved when using
UNION ALL?
Or can somebody recommend an optimal way to deal with the issue which
doesn't involve running two separate queries?
Thanks in advance,
-- Alberto
