| re: UNION und ORDER BY in 4.1.12 don't seem to work
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:[color=blue]
> 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
> )[/color]
| 
