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 '"'
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 '"'
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 '"'
On Sun, 2 Feb 2020 09:57:04 +0000 (UTC),
vjp2.at@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.
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@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.
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl>
wrote:
This is not right, it might give look like the correct result, but it isYeah, 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.
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
On Sat, 23 May 2020 10:13:00 +0200, Luuk <luuk34@gmail.com> wrote:
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl>This is not right, it might give look like the correct result, but it is
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.
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.
On 23-5-2020 15:49, Richard Yates wrote:
On Sat, 23 May 2020 10:13:00 +0200, Luuk <luuk34@gmail.com> wrote:
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl> >>>> wrote:This is not right, it might give look like the correct result, but it is >>> not guaranteed the correct result.
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.
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. "
On Sat, 23 May 2020 17:04:43 +0200, Luuk <luuk@invalid.lan> wrote:
On 23-5-2020 15:49, Richard Yates wrote:
On Sat, 23 May 2020 10:13:00 +0200, Luuk <luuk34@gmail.com> wrote:
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl> >>>>> wrote:This is not right, it might give look like the correct result, but it is >>>> not guaranteed the correct result.
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.
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.
On Sat, 23 May 2020 19:11:00 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:
On 5/23/2020 2:14 PM, Richard Yates wrote:
On Sat, 23 May 2020 17:04:43 +0200, Luuk <luuk@invalid.lan> wrote:
On 23-5-2020 15:49, Richard Yates wrote:
On Sat, 23 May 2020 10:13:00 +0200, Luuk <luuk34@gmail.com> wrote:
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl> >>>>>>> wrote:This is not right, it might give look like the correct result, but it is >>>>>> not guaranteed the correct result.
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.
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"?
On 5/23/2020 2:14 PM, Richard Yates wrote:
On Sat, 23 May 2020 17:04:43 +0200, Luuk <luuk@invalid.lan> wrote:
On 23-5-2020 15:49, Richard Yates wrote:
On Sat, 23 May 2020 10:13:00 +0200, Luuk <luuk34@gmail.com> wrote:
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl> >>>>>> wrote:This is not right, it might give look like the correct result, but it is >>>>> not guaranteed the correct result.
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.
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.
On Sat, 23 May 2020 17:19:09 -0700, Richard Yates
<richard@yatesguitar.com> wrote:
On Sat, 23 May 2020 19:11:00 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:
On 5/23/2020 2:14 PM, Richard Yates wrote:
On Sat, 23 May 2020 17:04:43 +0200, Luuk <luuk@invalid.lan> wrote:
On 23-5-2020 15:49, Richard Yates wrote:
On Sat, 23 May 2020 10:13:00 +0200, Luuk <luuk34@gmail.com> wrote: >>>>>>
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl> >>>>>>>> wrote:This is not right, it might give look like the correct result, but it is
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.
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
TNP Ford Edsel blackI resent that.
On Sat, 23 May 2020 17:19:09 -0700, Richard Yates
<richard@yatesguitar.com> wrote:
On Sat, 23 May 2020 19:11:00 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:
On 5/23/2020 2:14 PM, Richard Yates wrote:
On Sat, 23 May 2020 17:04:43 +0200, Luuk <luuk@invalid.lan> wrote:
On 23-5-2020 15:49, Richard Yates wrote:
On Sat, 23 May 2020 10:13:00 +0200, Luuk <luuk34@gmail.com> wrote: >>>>>>
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl> >>>>>>>> wrote:This is not right, it might give look like the correct result, but it is
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.
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
On 24-5-2020 03:07, Richard Yates wrote:
On Sat, 23 May 2020 17:19:09 -0700, Richard Yates
<richard@yatesguitar.com> wrote:
On Sat, 23 May 2020 19:11:00 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:
On 5/23/2020 2:14 PM, Richard Yates wrote:
On Sat, 23 May 2020 17:04:43 +0200, Luuk <luuk@invalid.lan> wrote:
On 23-5-2020 15:49, Richard Yates wrote:
On Sat, 23 May 2020 10:13:00 +0200, Luuk <luuk34@gmail.com> wrote: >>>>>>>
On 23-5-2020 01:47, Richard Yates wrote:
On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <k.nuyt@nospam.demon.nl>This is not right, it might give look like the correct result, but it is
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.
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
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 185 |
| Nodes: | 16 (1 / 15) |
| Uptime: | 87:28:32 |
| Calls: | 3,750 |
| Files: | 11,172 |
| Messages: | 3,462,270 |