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
)
