On 2016/03/09 6:07 PM, Alberto Wu wrote:
> 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?Firstly, this is the best method - one I would use. UNION ALL is quite efficient. Secondly, the order by will be honoured - you can refer to the SQL standard for that even, it's an axiom of the output and probably not even considered "needed" to mention. A point that might be worth considering: Union all will not rid duplicates and it's probably possible to get results like: pen pen pen tree If there are two legitimate copies of pen in your input data and another containing a space at the end and you use the RTRIM collation. That may or may not be what you want, but should be very easy to deal with either way. Cheers, Ryan
