Re: Column headings in csv

On 5/23/2020 2:14 PM, Richard Yates wrote:
> 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.
> 

I agree with Luuk - this isn't a good construct.  As for it "working" - 
I suspect it's just a hole in the SQL parser code.  Adding LIMIT to a 
clause should not change the validity of ORDER.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
[email protected]
==================