[Resolved] UNION query does not work

Joseph_Sliker

I have looked through serveral of the threads here having to do with MySQL UNION queries, but I can't seem to get one to work.

I have several data tables with similar structures. One date column in each is called "TESTDATE". I want to grab all those test dates from all of my tables, sort them (by date) so I can find the earliest and most recent.

I've tried entering the following raw query right in the MySQL monitor:

SELECT TESTDATE FROM DATAIGDIS UNION SELECT TESTDATE FROM DATAMATH ORDER BY TESTDATE;

and always get bupkis, nada, nothin' except

"ERROR 1064: You have an error in your SQL syntax near 'UNION SELECT TESTDATE FROM DATAMATH ORDER BY TESTDATE' at line 1"

I've tried wrapping the select statements in parentheses, including the ORDER BY statement in each select, and various other permutations.

The MySQL version I am running under is 4.3.3

Grrrrr.

Any ideas?

Thanks

nemonoman

Might be time to RTFM.

For starters:

f you want to use an ORDER BY to sort the entire UNION result, you should use parentheses:

(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;

http://www.mysql.com/doc/en/UNION.html

Lars_Berg

The MySQL version I am running under is 4.3.3

There is no such version of Mysql.

The error means that the Mysql version you are using does not support union.

Support for union was added in 4.0.. something.

f you want to use an ORDER BY to sort the entire UNION result, you should use parentheses:

No, that's not needed. An order by as specifed by in the first post will be applied on the result of the union. That it should be applied to the second select only does not make any sense.

Joseph_Sliker

Ooops,

That was php v. 4.3.3

Turns out I'm working under MySQL 3.23.45 ... there's the rub!

I will have to see if I can get the Powers That Be to upgrade.

Duh!

Thanks.