473,832 Members | 2,107 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem using MyODBC (Linux version) - bracketed UNION

Dear All

I've come across a strange issue when using UNION via MyODBC on my
Fedora linux box (I'm using MySQL 4.1.3-standard-beta)

**This doesn't work:

(select a from tbl_name where a=10 and b=1 order by a)
union
(select a from tbl_name where a=11 and b=2 order by a)
order by 1

(took this straight from the MySQL manual)

**Removing parentheses doesn't work either:

select a from tbl_name where a=10 and b=1 order by a
union
select a from tbl_name where a=11 and b=2 order by a
order by 1

**But this does work:

select a from tbl_name where a=10 and b=1
union
select a from tbl_name where a=11 and b=2
order by 1
- so it appears that ORDER BY is not allowed in an individual select
when it is being amalgamated with UNION (or UNION ALL).

Interestingly, the first statement works fine when executing from the
MySQL command line.

Has anyone else spotted this, and is there a workaround (other than
not ordering select clauses that are UNIONed together)?

Regards
Adrian
Jul 20 '05 #1
1 2120
Adrian wrote:
- so it appears that ORDER BY is not allowed in an individual select
when it is being amalgamated with UNION (or UNION ALL).


It could be that ODBC is doing some pre-parsing to ensure valid SQL.
The feature of ordering each component query and then ordering the
resulting union may be a nonstandard feature (I'm not certain of this),
and should rightly be disallowed in standard SQL.

In any case, why are you trying to order each query if you're just going
to re-order the resulting union? The preliminary ordering is
effectively a no-op, and therefore a waste of work.

Regards,
Bill K.
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
478
by: Adrian | last post by:
Dear All I've come across a strange issue when using UNION via MyODBC on my Fedora linux box (I'm using MySQL 4.1.3-standard-beta) **This doesn't work: (select a from tbl_name where a=10 and b=1 order by a) union (select a from tbl_name where a=11 and b=2 order by a)
1
2769
by: Boris Wilhelms | last post by:
Hello all, at first, sorry for my bad English, I’ll give my best  We have a strange problem reading Text- and VarChar-Fields. Our configuration: -Windows 2003 Server -MySQL Server 3.23.36 -MyODBC 3.51
6
4570
by: Murtix Van Basten | last post by:
HI all, I am trying to migrate a database from mysql to mssql2k. I use myODBC to connect to mysql server to pull the database from DTS and insert in to sql server. But in mysql server, there is one huge table. Size of it is 1.3 GB and more than 12 million rows. While I try with DTS, it transfers all tables in the database except this huge table. First I thought it would be the resources of the computer not enough (512MB physical ram...
5
1920
by: Dave Townsend | last post by:
Hi, I'm trying to port a piece of code from Windows to Linux, the following segment illustrates the coding problemI have: Under Linux, the reinterpret_cast line doesn't compile, the compiler says ISO C++ doesn allow a cast from pointer-to-object to pointer-to-function. However, I can use the plain-old-C-style cast and the code compiles and works. I guess I have two questions:-
9
3211
by: zMisc | last post by:
When I try to update record, I kept getting this error: Row cannot be located for updating. Some values may have been changed since it was last read. No other users are accessing the database - only 1 user is accessing the database. I open the data using adLockOptimistic locking.
6
3988
by: Piotr | last post by:
Hi, I have following problem: I use a form in excel to send data into mysql server, everything is ok unless I have to deal with decimals or data fields, this simple are not recognized. For example In excel in mySQL 45,45 -> 45 2005-01-01 -> 0000-00-00
7
1888
by: Bob Stearns | last post by:
Several weeks ago I asked what comments I could pass to DB2 in a SELECT statement. I don't remember whether I said via PHP/ODBC. I was assured that both /* */ style comment blocks and -- comment lines were allowed. Unfortunately, neither work. Both -- and /* */ comments cause a "premature end of statement". Any sage advice?
4
4142
by: google | last post by:
Hi Hope someone can help me with this - have been staring at this problem all day, and with the cold I've got, just don't seem to be able to figure it out! I've got two tables, here with some test data - sorry, can't create DDL as I'm at home & can't access the server :-( TABLE 1: Application_Intermediary
3
3907
by: sp3d2orbit | last post by:
I've read the MySQL licensing material and what I've found online, but I'm still unclear about when I have to pay MySQL for a license. Scenario: I've created an application that stores some data in MySQL 5.0 (InnoDB table types). It connects to MySQL 5.0 via MyODBC. 1.) If I include MySQL 5.0 and MyODBC in the installer, do I have to buy a separate MySQL license for every copy of the installer I sell? 2.) If I include MySQL 5.0 and...
5
2255
by: Control Freq | last post by:
Hi I connect to my MySQL database using MyODBC. I have a table called 'log' in a database called 'home'. When I try to do an insert like this: insert into log (userid,event,event_date) values (blah blah.... I get this error response from MySQL: You have an error in your SQL syntax; check the manual that corresponds
0
9796
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10782
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10213
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9323
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7753
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6951
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5624
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5789
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3972
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.