Re: Column headings in csv

On Sat, 23 May 2020 17:04:43 +0200, Luuk <[email protected]> wrote:

>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. "

Yes, I understand what the manual says but quoting the manual does not
answer my question. I would point out two things:

1. Without the LIMITs in the two parts, an error is indeed thrown by
mysql, but with the limits it passes, suggesting that mysql thinks
that the query is proper. 

2. The manual describes the "default" behavior. It seems that the
default may be overridden in some circumstances, such as the query I
used. That is even the definition of "default". The manual does not
say "always produces an unordered set"

Nevertheless, the query that I used works. Since you said that the
correct result was not guaranteed, I was asking if you knew in which
circumstances it would not work, or reasons that it would not work.