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
