Re: Column headings in csv

On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <[email protected]>
wrote:

>On Sun, 2 Feb 2020 09:57:04 +0000 (UTC),
>[email protected] wrote:
>
>> I googled this and thought it would work, but somewhere along the edges, I
>>messed it up
>>
>> First I tried ; instead of union and it put the csv file in the directory
>> with the DB not where I usually want the results.  The the first SELECT on
>> its own creates a file where each col name is a line on its own.
>>
>> Then it doesn't like the statement below
>> Error Code: 1221. Incorrect usage of UNION and ORDER BY
>
>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.