Ordering union

[itxtme]

I have a Union hat I need to order, however I am struggling to get the order correct. I have two tables that are correctly pulling the data. The two order columns use an auto increment to give it an order number. So now I have matching numbers in the columns. This means that ordering goes

A.1
B.1
A.2
B.2

What I really want is the order to be

A.1
A.2
B.1
B.2

I have tried adding names to the columns and ordering by A.orders, B.orders but this wont work. Any ideas for me guys???

SELECT `order`, field_name FROM all_child_fields WHERE editible='1' UNION SELECT `order`, field_name FROM a_child_fields WHERE editible='1' ORDER BY `order`

[BubikolRamios]

something like that:

select * from 
(
SELECT `order`, field_name FROM all_child_fields WHERE editible='1' 
UNION 
SELECT `order`, field_name FROM a_child_fields WHERE editible='1' 
) as foo
ORDER BY foo.order, foo.field_name

[itxtme]

@BubikolRamios

Thanks for the suggestion, I did attempt that prior to my first post but it doesnt work. I have decided to add a 3rd column that is 0 for one table and 1 for the next, I can then order by this column followed by the order column

[Old Pedant]

You don't need to add a column to the tables. Just add it to the UNION.

If that's what you meant, then that is the correct way to do it.

SELECT 0 as tablenumber, `order`, FROM all_child_fields WHERE editible='1' 
UNION 
SELECT 1, `order`, field_name FROM a_child_fields WHERE editible='1' 
ORDER BY tablenumber, `order`