On 2016/03/10 8:37 PM, James K. Lowden wrote: > On Thu, 10 Mar 2016 10:17:57 +0100 > Alberto Wu <blu at skylable.com> 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
