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
Easy enough, but alas!, the amount of places I have to go and change... :)
Thanks,
Ryan
