[sqlite] "Circular" order by
On Fri, 11 Mar 2016 09:05:27 +0200, R Smith wrote: > >On 2016/03/11 5:52 AM, Stephen Chrzanowski wrote: >> On Thu, Mar 10, 2016 at 2:16 PM, R Smith wrote: >> >> >>> I do this kind of thing so often when filling a selection box for instance: >>>SELECT 'None' >>> UNION ALL >>>SELECT City FROM Countrylist WHERE Country = :1 >>> UNION ALL >>> SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City >>> >>> Which, as you can deduce, adds a 'None' to the option list, then the >>> selected country's capital city, then the other cities in alphabetical >>> order. I now think I need a more sophisticated method to ensure that output >>> doesn't get mangled. If SQLite ever changes this behaviour, lots of things >>> will break for me, but, that's life, I will start fixing them all. >>> >>> Heh, assumptions... that'll teach me! :) >>> Ryan >>> >> Not that I want to hijack the thread, but with the country list I got from >> here: >> https://raw.githubusercontent.com/umpirsky/country-list/master/data/en/country.sqlite.sql >> >> I came up with this simple modification to your query: >> >> SELECT 'None',0 as OrderNum >> UNION ALL >>SELECT Value,1 FROM List WHERE Value = :1 >> UNION ALL >>SELECT Value,2 FROM List WHERE Value <> :1 ORDER BY OrderNum,Value > >Yeah, though I think this one might still be in danger of not ending up >in the correct order since the UNIONed section, although able to order, >doesn't guarantee order, nor influences the order in the other UNIONed >sections and output as a whole... a bit weird, but understandable. So >the foolproof way I think would be adapting your advice into this: > >SELECT V FROM ( > SELECT 'None' AS V, 0 as Ord > UNION ALL > SELECT Value,1 FROM List WHERE SomeIndex == :1 > UNION ALL > SELECT Value,2 FROM List WHERE SomeIndex <> :1 >) ORDER BY Ord, V That is not needed, as http://www.sqlite.org/lang_select.html#orderby says: "The ORDER BY clause [] In a compound SELECT statement, only the last or right-most simple SELECT may have an ORDER BY clause. That ORDER BY clause will apply across all elements of the compound. If the right-most element of a compound SELECT is a VALUES clause, then no ORDER BY clause is allowed on that statement." > Easy enough, but alas!, the amount of places I have to go and change... :) HTH ;) -- Regards, Kees Nuyt
[sqlite] "Circular" order by
On 2016/03/11 5:52 AM, Stephen Chrzanowski wrote: > On Thu, Mar 10, 2016 at 2:16 PM, R Smith wrote: > > >> I do this kind of thing so often when filling a selection box for instance: >>SELECT 'None' >> UNION ALL >>SELECT City FROM Countrylist WHERE Country = :1 >> UNION ALL >> SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City >> >> Which, as you can deduce, adds a 'None' to the option list, then the >> selected country's capital city, then the other cities in alphabetical >> order. I now think I need a more sophisticated method to ensure that output >> doesn't get mangled. If SQLite ever changes this behaviour, lots of things >> will break for me, but, that's life, I will start fixing them all. >> >> Heh, assumptions... that'll teach me! :) >> Ryan >> > Not that I want to hijack the thread, but with the country list I got from > here: > https://raw.githubusercontent.com/umpirsky/country-list/master/data/en/country.sqlite.sql > > I came up with this simple modification to your query: > > SELECT 'None',0 as OrderNum > UNION ALL >SELECT Value,1 FROM List WHERE Value = :1 > UNION ALL >SELECT Value,2 FROM List WHERE Value <> :1 ORDER BY OrderNum,Value Yeah, though I think this one might still be in danger of not ending up in the correct order since the UNIONed section, although able to order, doesn't guarantee order, nor influences the order in the other UNIONed sections and output as a whole... a bit weird, but understandable. So the foolproof way I think would be adapting your advice into this: SELECT V FROM ( SELECT 'None' AS V, 0 as Ord UNION ALL SELECT Value,1 FROM List WHERE SomeIndex == :1 UNION ALL SELECT Value,2 FROM List WHERE SomeIndex <> :1 ) ORDER BY Ord, V Easy enough, but alas!, the amount of places I have to go and change... :) Thanks, Ryan
[sqlite] "Circular" order by
On Thu, Mar 10, 2016 at 2:16 PM, R Smith wrote: > > I do this kind of thing so often when filling a selection box for instance: > SELECT 'None' > UNION ALL > SELECT City FROM Countrylist WHERE Country = :1 > UNION ALL > SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City > > Which, as you can deduce, adds a 'None' to the option list, then the > selected country's capital city, then the other cities in alphabetical > order. I now think I need a more sophisticated method to ensure that output > doesn't get mangled. If SQLite ever changes this behaviour, lots of things > will break for me, but, that's life, I will start fixing them all. > > Heh, assumptions... that'll teach me! :) > Ryan > Not that I want to hijack the thread, but with the country list I got from here: https://raw.githubusercontent.com/umpirsky/country-list/master/data/en/country.sqlite.sql I came up with this simple modification to your query: SELECT 'None',0 as OrderNum UNION ALL SELECT Value,1 FROM List WHERE Value = :1 UNION ALL SELECT Value,2 FROM List WHERE Value <> :1 ORDER BY OrderNum,Value
[sqlite] "Circular" order by
On 03/10/16 20:16, R Smith wrote: >> Hmm, does this work any better? >> >> SELECT id FROM t >> ORDER BY id < 'pen' desc, id; > > It works, but not better. Indeed. Any operation in ORDER BY (even things like "ORDER BY id||''") result in scan + temp b-tree. Thanks again. -- Alberto
[sqlite] "Circular" order by
On 2016/03/10 8:37 PM, James K. Lowden wrote: > On Thu, 10 Mar 2016 10:17:57 +0100 > Alberto Wu wrote: > >> On 03/09/16 23:30, James K. Lowden wrote: SELECT P.id FROM ( SELECT 0 AS sect, id FROM t WHERE id >= 'pen' UNION ALL SELECT 1, id FROM t WHERE id < 'pen' ) AS P ORDER BY P.sect, P.id ; >>> This is the correct answer. >> Hi, >> >> unfortunately the correct answer comes with an extra scan and a temp >> b-tree so I'd rather keep the two queries split and handle the case >> programmatically. > Hmm, does this work any better? > > SELECT id FROM t > ORDER BY id < 'pen' desc, id; It works, but not better. I think it was Igor who proposed similar (if not, apologies) which of course produces the correct result, but cannot take advantage of the index on id so it becomes a result-set walk causing longer ordering of values - exactly what the OP tried to avoid. I myself is sad to find that the SELECTs in between UNION ALL statements qualify as sub-selects and do not implicitly honor the order in which they are UINIONed - I had this wrong. I do this kind of thing so often when filling a selection box for instance: SELECT 'None' UNION ALL SELECT City FROM Countrylist WHERE Country = :1 UNION ALL SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City Which, as you can deduce, adds a 'None' to the option list, then the selected country's capital city, then the other cities in alphabetical order. I now think I need a more sophisticated method to ensure that output doesn't get mangled. If SQLite ever changes this behaviour, lots of things will break for me, but, that's life, I will start fixing them all. Heh, assumptions... that'll teach me! :) Ryan
[sqlite] "Circular" order by
On Thu, 10 Mar 2016 21:16:28 +0200 R Smith wrote: > > Hmm, does this work any better? > > > > SELECT id FROM t > > ORDER BY id < 'pen' desc, id; > > It works, but not better. I think it was Igor who proposed similar > (if not, apologies) which of course produces the correct result, but > cannot take advantage of the index on id so it becomes a result-set > walk causing longer ordering of values - exactly what the OP tried to > avoid. Hmm, I don't know about "cannot", but I'm not surprised by "does not", because it's a tough inference. If you stand back a minute, you can see that id < 'pen' is a monotonic function of "id" if "id" is sorted. The query processor *could* include that logic, and could choose to process the rows, in index order, starting with the first row where id >= 'pen' , to the end, and wrapping back to the beginning. The big boys do that kind of thing. In general "order by f(x)" will use an index on x if f(x) has the same order. For hard problems, they support computed columns -- a little like a view attached to a table -- and indexes on them. In SQLite, I guess the OP's only solution is to make an index of the kind he needs. create table idx as select id < 'pen' as 'LT_pen', id from t; select id from idx order by LT_pen; Of course, that presupposes 'pen' is a constant. Whether or not that's true wasn't mentioned in the original post. --jkl
[sqlite] "Circular" order by
On Thu, 10 Mar 2016 10:17:57 +0100 Alberto Wu wrote: > On 03/09/16 23:30, James K. Lowden wrote: > >> SELECT P.id FROM ( > >> SELECT 0 AS sect, id FROM t WHERE id >= 'pen' > >> UNION ALL > >> SELECT 1, id FROM t WHERE id < 'pen' > >> ) AS P > >> ORDER BY P.sect, P.id > >> ; > > > > This is the correct answer. > > Hi, > > unfortunately the correct answer comes with an extra scan and a temp > b-tree so I'd rather keep the two queries split and handle the case > programmatically. Hmm, does this work any better? SELECT id FROM t ORDER BY id < 'pen' desc, id; --jkl
[sqlite] "Circular" order by
On 03/09/16 23:30, James K. Lowden wrote: >> SELECT P.id FROM ( >> SELECT 0 AS sect, id FROM t WHERE id >= 'pen' >> UNION ALL >> SELECT 1, id FROM t WHERE id < 'pen' >> ) AS P >> ORDER BY P.sect, P.id >> ; > > This is the correct answer. Hi, unfortunately the correct answer comes with an extra scan and a temp b-tree so I'd rather keep the two queries split and handle the case programmatically. Thanks a lot for your help. -- Alberto
[sqlite] "Circular" order by
On 2016/03/09 8:08 PM, Alberto Wu wrote: > On 03/09/16 17:26, R Smith wrote: >> 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. > Ryan, > Thanks a lot for the quick reply. > > I had thought about the SQL axiom too. > However I've also noticed that sqlite actively forbids ORDER BY clauses > in each SELECT when using UNION / UNION ALL, which makes that assumption > kind of dull. > In fact in order to achieve this per-SELECT sorting rather than the > sorting of the combined result set you have to trick sqlite through the > use of the WITH construct or via a subquery in FROM (like in my example). > So in the end I thought I'd just be safe and ask :) Quite right, apologies, I meant to say that your example was the correct way and expected to work, not the initial assumption (which already is obvious to not work in SQLite). i.e. this: SELECT * FROM (SELECT * FROM t WHERE id >= 'pen' ORDER BY id) UNION ALL SELECT * FROM (SELECT * FROM t WHERE id < 'pen' ORDER BY id) ; As long as you don't impose another order-by on an outer query, nor impose the UNION (without the ALL)[1], the output is simply appended and will be correctly sorted. As a point of interest, is this query really much slower for you? It should produce the exact same order: SELECT 0 AS Sect, * FROM t WHERE id >= 'pen' UNION SELECT 1, * FROM t WHERE id < 'pen' ; You could also wrap the above inside another query, keeping the UNION ALL, stripping the Sect field and doing your own Order by, like so: SELECT P.id FROM ( SELECT 0 AS sect, id FROM t WHERE id >= 'pen' UNION ALL SELECT 1, id FROM t WHERE id < 'pen' ) AS P ORDER BY P.sect, P.id ; Test to find the fastest case :) Cheers! Ryan [1] - When using UNION only, SQLite might re-order the output set to be able to quicker check for duplications
[sqlite] "Circular" order by
On 03/09/16 17:26, R Smith wrote: > 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. Ryan, Thanks a lot for the quick reply. I had thought about the SQL axiom too. However I've also noticed that sqlite actively forbids ORDER BY clauses in each SELECT when using UNION / UNION ALL, which makes that assumption kind of dull. In fact in order to achieve this per-SELECT sorting rather than the sorting of the combined result set you have to trick sqlite through the use of the WITH construct or via a subquery in FROM (like in my example). So in the end I thought I'd just be safe and ask :) > A point that might be worth considering: Union all will not rid > duplicates and it's probably possible to get results like: That's perfectly fine: like in my example the column is unique in my real life case as well. Besides, UNION does not honour the original like UNION ALL does. > 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. The actual column type is BINARY but thanks for the tip anyway! Thanks again, -- Alberto
[sqlite] "Circular" order by
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
[sqlite] "Circular" order by
On Wed, 9 Mar 2016 20:43:14 +0200 R Smith wrote: > SELECT P.id FROM ( > SELECT 0 AS sect, id FROM t WHERE id >= 'pen' > UNION ALL > SELECT 1, id FROM t WHERE id < 'pen' > ) AS P > ORDER BY P.sect, P.id > ; This is the correct answer. I'm not sure what you meant by "axiom" in your earlier post. To the best of my knowledge, UNION ALL makes no promise about order (and standard SQL proscribes ORDER BY in a subquery). If the implementation finds it more efficient, for instance, to send alternating rows from each element in the union, it's free to do so. --jkl
[sqlite] "Circular" order by
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
