Column headings in csv

[vjp...@at.biostrategist.dot.dot.com]

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

For what it's worth I need to put it into Open Office DB bacuse the folks who
want this can't afford to buy Access. Haven't done it before.
Will be a million records out of a multi million record Oracle MySQL db.

select concat ( COLUMN_NAME , " ," ) as column_name
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = "z1"
order by ORDINAL_POSITION

union
select * from z1 where z3=41
into outfile 'z2'
fields terminated by ','
enclosed by '"'

[Kees Nuyt]

On Sun, 2 Feb 2020 09:57:04 +0000 (UTC),
vjp...@at.BioStrategist.dot.dot.com 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.

> For what it's worth I need to put it into Open Office DB bacuse the folks who
> want this can't afford to buy Access. Haven't done it before.
> Will be a million records out of a multi million record Oracle MySQL db.
>
> select concat ( COLUMN_NAME , " ," ) as column_name
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = "z1"
> order by ORDINAL_POSITION
>
> union
> select * from z1 where z3=41
> into outfile 'z2'
> fields terminated by ','
> enclosed by '"'

With a client, you could use redireection and two queries:

mysql [options] -e 'the-select-of_the_column_names' >outfile.csv
mysql [options] -e 'the-select-of_the_data' >>outfile.csv

Of course you'll have to leave out the "into outfile" clause.
(untested)
--
Regards,
Kees Nuyt

[Chris Elvidge]

Have you seen this?
https://github.com/bobby96333/csvdump
It might help.
Or dbeaver : https://dbeaver.io/

Note these are suggestions from stackoverflow. Not tested by me.


--

Chris Elvidge, England

[vjp...@at.biostrategist.dot.dot.com]

Fixed by parens, but it seems I need to do transpose (as in linear algebra),
flip a column inot a row



- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus
blog: panix.com/~vjp2/ruminatn.htm - = - web: panix.com/~vjp2/vasos.htm
facebook.com/vasjpan2 - linkedin.com/in/vasjpan02 - biostrategist.com
---{Nothing herein constitutes advice. Everything fully disclaimed.}---

[Chris Elvidge]

On 02/02/2020 09:57, vjp...@at.BioStrategist.dot.dot.com wrote:

Or try this
In mysql:

create table z1t like z1;
insert into z1t

select * from z1 where z3=41

Then go to OpenOfficeDB and import the new table
Gets round all the problems with quotes/spaces in fields.

--

Chris Elvidge, England

[Luuk]

https://stackoverflow.com/questions/12040816/mysqldump-in-csv-format

SELECT customer_id, firstname, surname INTO OUTFILE
'/exportdata/customers.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customers;

--
Luuk

[Richard Yates]

On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.n...@nospam.demon.nl>
wrote:

>On Sun, 2 Feb 2020 09:57:04 +0000 (UTC),
>vjp...@at.BioStrategist.dot.dot.com 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.

[Luuk]

This is not right, it might give look like the correct result, but it is
not guaranteed the correct result.

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

[Richard Yates]

On Sat, 23 May 2020 10:13:00 +0200, Luuk <luu...@gmail.com> wrote:

>On 23-5-2020 01:47, Richard Yates wrote:
>> On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.n...@nospam.demon.nl>
>> 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?

>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]

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

[Richard Yates]

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.

[Jerry Stuckle]

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
jstu...@attglobal.net
==================

[Richard Yates]

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

[Richard Yates]

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

[Jerry Stuckle]

So? A hole in the parser code may allow it to work - and it might even
work correctly - for now. But that can change at any time.
Undocumented behavior is *never* safe.

[The Natural Philosopher]

On 24/05/2020 02:07, Richard Yates wrote:
> TNP Ford Edsel black
I resent that.

Jaguar XF Blue.

--
"I am inclined to tell the truth and dislike people who lie consistently.
This makes me unfit for the company of people of a Left persuasion, and
all women"

[Luuk]

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




--
Luuk

[Richard Yates]

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.

[Luuk]

Try this:

WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)

SELECT * FROM cte;


The 'WITH [RECURSIVE]' defines a temporry table with the data from the
select statement that is following it.

The 'SELECT * from cte' shows the output of the select, in this case
numbers from 1 to 5;

In this case it is used to create a temporary table to have some data
for a simple example....

--
Luuk