UNION und ORDER BY in 4.1.12 don't seem to work

[Tom Schindl]

Hi,

the following Statement worked on MySQL 4.0 but after upgrading to
4.1.12 on win32 the order is not working any more. Is this a known
problem or is our SQL simply not useable on 4.1 or is there a bug in
MySQL 4.1.

----------------------------8<----------------------------
(
select
p.*
from
persons p,
client_contracts cc
where
cc.cc_fk_person = p.p_id and
p.delmark = 0 and
cc.delmark = 0 and
p.p_id = 0 -- only mohi
)
UNION
(
select
p.*
from
persons p,
client_contracts cc
where
cc.cc_fk_person = p.p_id and
p.delmark = 0 and
cc.delmark = 0 and
p.p_id != 0 -- no mohi
order by
p.p_surname,
p.p_givenname
)
----------------------------8<----------------------------

Should return:
---------------------------------
| p_id | p_givenname | p_surname|
---------------------------------
| 0 | MOHI | MOHI | => First part of union
---------------------------------
| 1 | Vorname | ANachname|
---------------------------------
| 100 | Vorname | BNachname|
---------------------------------
| 3 | Vorname | CNachname|
---------------------------------
But it returns:
---------------------------------
| p_id | p_givenname | p_surname|
---------------------------------
| 0 | MOHI | MOHI | => First part of union
---------------------------------
| 1 | Vorname | ANachname|
---------------------------------
| 3 | Vorname | CNachname|
---------------------------------
| 100 | Vorname | BNachname|
---------------------------------

Any ideas?

Thanks

Tom

[hoonew]

You need the ORDER BY after the last parethesis.

Check out the docs: 13.2.7.2. UNION Syntax

ORDER BY for individual SELECT statements within parentheses only has an
effect when combined with LIMIT. Otherwise, the ORDER BY is optimized away.

If you want to use an ORDER BY or LIMIT clause to sort or limit the
entire UNION result, parenthesize the individual SELECT statements and
place the ORDER BY or LIMIT after the last one. The following example
uses both clauses:

(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 a LIMIT 10;
Tom Schindl wrote:

Hi,

the following Statement worked on MySQL 4.0 but after upgrading to
4.1.12 on win32 the order is not working any more. Is this a known
problem or is our SQL simply not useable on 4.1 or is there a bug in
MySQL 4.1.

----------------------------8<----------------------------
(
select
p.*
from
persons p,
client_contracts cc
where
cc.cc_fk_person = p.p_id and
p.delmark = 0 and
cc.delmark = 0 and
p.p_id = 0 -- only mohi
)
UNION
(
select
p.*
from
persons p,
client_contracts cc
where
cc.cc_fk_person = p.p_id and
p.delmark = 0 and
cc.delmark = 0 and
p.p_id != 0 -- no mohi
order by
p.p_surname,
p.p_givenname
)