Re: Problems with UNION and ORDER BY

[steve ralph]

Hi just to let you know that I have solved the problem

I Just placed tm from the AS clause into the ORDER BY at the end of the query. Seems to work fine now.

DATE_FORMAT(db1.tbl1.tbl_date, '%M %d, %Y') AS tm

$query = "(SELECT CONCAT(a_item1) AS i1, (a_item2) AS i2, (a_item3) AS i3, DATE_FORMAT(db1.tbl1.tbl_date, '%M %d, %Y') AS tm FROM db1.tbl1, db1.tbl2 WHERE status = '6' AND tbl1_ID = tbl2_ID ORDER BY db1.tbl1.tbl_date DESC LIMIT 6)
UNION ALL
SELECT CONCAT(b_item1) AS i1, (b_item2) AS i2, (b_item3) AS i3, DATE_FORMAT(db2.tbl1.tbl_date, '%M %d, %Y') AS tm FROM db2.tbl1, db2.tbl2 WHERE status = '6' AND tbl1_ID = tbl2_ID ORDER BY db2.tbl1.tbl_date DESC LIMIT 6) ORDER BY tm DESC LIMIT 6";