Re: Column headings in csv

On 23-5-2020 15:49, Richard Yates wrote:
> On Sat, 23 May 2020 10:13:00 +0200, Luuk <[email protected]> wrote:
> 
>> On 23-5-2020 01:47, Richard Yates wrote:
>>> On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <[email protected]>
>>> wrote:
>>>
>>
>>>> Yeah, you can only ORDER the result of the UNION, not on of its
>>>> parts.
>>>
>>> Actually you can if you order each part and then set a limit. Recently
>>> I found this out and am using this (in a php application).
>>>
>>> (Distribution sites are each assigned to one of several districts. The
>>> query pulls out the ones from one of the districts to list first at
>>> the top of a dropdown menu, and then lists the remaining ones.)
>>>
>>> $distsitesq="
>>> (select distsite, name from distsites
>>> where ID_district=$ID_district
>>> order by name limit 999) union
>>> (select distsite, name from distsites
>>> where ID_district<>$ID_district
>>> order by name limit 999)";
>>>
>>> 999 is chosen because in this example it is far larger than the
>>> distsites table will ever be. Setting the limit enforces the ordering
>>> of each part.
>>>
>> This is not right, it might give look like the correct result, but it is
>> not guaranteed the correct result.
> 
> It does produce the correct result in my application. How would it not
> produce that result?

https://dev.mysql.com/doc/refman/8.0/en/union.html

"Use of ORDER BY for individual SELECT statements implies nothing about 
the order in which the rows appear in the final result because UNION by 
default produces an unordered set of rows. "

> 
>> The correct (SQL) way to do this is like this:
>>
>> select 1 as x,distsite, name from distsites
>>   where ID_district=$ID_district
>>   union
>>   select 2 as x, distsite, name from distsites
>>   where ID_district<>$ID_district
>> order by x,name
> 
> Thank you for the suggestion. It makes sense and I will try it, also.
> 


-- 
Luuk