Re: Column headings in csv

On Sun, 24 May 2020 11:34:45 +0200, Luuk <[email protected]> wrote:

>On 24-5-2020 03:07, Richard Yates wrote:
>> On Sat, 23 May 2020 17:19:09 -0700, Richard Yates
>> <[email protected]> wrote:
>> 
>>> On Sat, 23 May 2020 19:11:00 -0400, Jerry Stuckle
>>> <[email protected]> wrote:
>>>
>>>> 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.
>>>
>>> I am not at all claiming that it is better or even good. And I have
>>> substituted in Luuk's suggestion (thanks again!) which also showed me
>>> a clever technique that I can probably use in other places.
>>>
>>> But wouldn't a "hole in the parser code" just account for it not
>>> throwing an error and not explain why it really does work? I tried
>>> scrambling the order of the table just to see if the fact that it
>>> worked was just an artifact of a particular order, but it still works.
>>> No matter what I do I cannot make it not work.
>>>
>>> So perhaps, as you say, adding a LIMIT to a clause (in a union) SHOULD
>>> not change the validity of ORDER in the clause in some hypothetical
>>> moral domain, but it seems that it actually does do that in unions.
>>>
>>> Why not call this an undocumented feature rather than a "hole"?
>> 
>> Still cannot break it. Did discover that the parentheses around each
>> part are necessary.
>> 
>> (select owner, make, model, color from cars
>> where owner='Jerry'
>> order by make, model limit 10)
>> union
>> (select owner, make, model, color from cars
>> where owner='Luuk'
>> order by color limit 10)
>> union
>> (select owner, make, model, color from cars
>> where owner<>'Luuk' and owner<>'Jerry'
>> order by model limit 10)
>> 
>> Jerry	Audi	A3	red
>> Jerry	Audi	TT	silver
>> Jerry	Ford	Fiesta	green
>> Jerry	Ford	Fiesta	blue
>> Jerry	GMC	Savannah	blue
>> Jerry	GMC	Sierra	blue
>> 
>> Luuk	GMC	Yukon	black
>> Luuk	Ford	F100	blue
>> Luuk	GMC	Savannah	blue
>> Luuk	Ford	Mustang	green
>> Luuk	Ford	Fiesta	red
>> 
>> Zor	Audi	A4	white
>> TNP	Ford	Edsel	black
>> 
>
>Ok, example, selecting the two lowest and the two highest values:
>
>WITH RECURSIVE cte (n) AS
>(
>   SELECT 1
>   UNION ALL
>   SELECT n + 1 FROM cte WHERE n < 5
>)
>(SELECT *
>FROM cte
>order by n
>limit 2 )
>union all
>(SELECT *
>FROM cte
>order by n desc
>limit 2 )
>order by n
>;
>
>1) The 'with recursive' creates a temporary view containing 5 records 
>from 1 until (including) 5.
>
>2) In the 'SELECT * FROM cte order by n limit 2', the ORDER BY is needed 
>to make sure where get the first two records. Because of the LIMIT there 
>is also a need to know where to start, this can only be done using ORDER BY.
>
>3) after the union all we do the same select, again with 'order by' to 
>make sure the highest two values are selected.
>
>4) the final order by is needed to produce 1,2,4,5  and not 1,2,5,4

Sorry, that example is beyond me and I do not know what it is intended
to show. And I've never run across RECURSIVE before.