Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: Ordering union

  1. #1
    Regular Coder
    Join Date
    Jun 2009
    Posts
    139
    Thanks
    3
    Thanked 20 Times in 20 Posts

    Ordering union

    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???

    Code:
    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`

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    something like that:
    Code:
    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
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Regular Coder
    Join Date
    Jun 2009
    Posts
    139
    Thanks
    3
    Thanked 20 Times in 20 Posts
    @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

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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.
    Code:
    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`
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •