On Fri, 11 Mar 2016 09:05:27 +0200, R Smith <rsmith at rsweb.co.za> wrote:
> >On 2016/03/11 5:52 AM, Stephen Chrzanowski wrote: >> On Thu, Mar 10, 2016 at 2:16 PM, R Smith <rsmith at rsweb.co.za> 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
