...

View Full Version : Ordering union



itxtme
02-03-2013, 09:26 PM
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
02-03-2013, 11:39 PM
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
02-04-2013, 01:15 AM
@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
02-04-2013, 09:09 PM
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`



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum