I have two tables from which I am generating a table with the results. I am using the UNION parameter to combine the two MySQL SELECT statements and ORDER BY to ensure that the results are ordered by the Datetime field.

The issue that I am faced with is that, the results are not being ORDERED correctly by date. Many of the newest results from the second table are found towards the end of the results. I tried the BINARY ORDER BY and the results worsened..

Is there a way to fix this issue? And have the results from both tables ORDERED by date?

MySQL version: 4.1.20
Table Type: MyISAM
Date_format field Type: datetime [the field is update through NOW()]

Code Snippet:

SELECT

tab1-ID,
date_format(tab1-date, '%M %D %W, %Y %l:%i %p') AS tab1-date,
tab1-email

FROM
table1
WHERE
tab1-email = 'test@foo.com'

UNION

SELECT
tab2-ID,
date_format(tab2-date, '%M %D %W, %Y %l:%i %p') AS tab2-date,
tab2-email
FROM
table2
WHERE
tab2-email = 'test@foo.com'
ORDER BY
2 DESC

-- End--

TIA

[edited by: HoboTraveler at 7:24 pm (utc) on Aug. 9, 2006]