467,122 Members | 1,329 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,122 developers. It's quick & easy.

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

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
Jul 23 '05 #1
  • viewed: 1476
Share:
1 Reply
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
)

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Blake Caraway | last post: by
5 posts views Thread by NAJH | last post: by
14 posts views Thread by Clint Olsen | last post: by
11 posts views Thread by Israel | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.