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
