Select from several identical tables

[makko]

I got several tables that are identical as structure. I need to select all the records from those tables,

filtered and ordered by date column .

 

So far no problem :

 

(SELECT * FROM table1 WHERE (Category Like '%Category')) UNION

(SELECT * FROM table2 WHERE (Category Like '%Category')) UNION

(SELECT * FROM table3 WHERE (Category Like '%Category')) UNION

(SELECT * FROM table4 WHERE (Category Like '%Category')) UNION

(SELECT * FROM table5 WHERE (Category Like '%Category')) ORDER BY Data

 

I want my records to be sorted by date as you see in the querry above ... but ... if there are several records

from table1 and table5 from the same day, it seems that MYSQL will return first the table1 records and after that

table5 records, even if some table5 records are newer.

 

for example

table1

1  aaa  2008-01-22

2  bbb 2008-01-21

 

table5

1  ccc  2008-01-27

2  ddd  2008-01-10

 

The correct result should be

2  ddd  2008-01-10

2  bbb 2008-01-21

1  aaa  2008-01-22

1  ccc  2008-01-27

 

The result I get

2  bbb 2008-01-21

1  aaa  2008-01-22

2  ddd  2008-01-10

1  ccc  2008-01-27

 

I tried the querry in SQL SERVER and I get the correct result every time

 

Where is my mistake ? or what can I change to get the correct result without puting the querry into a view or

using just one  table containing all the records

[fenway]

You have to wrap the entire thing in parens, then order by.