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
