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 8 of 8

Thread: union order by

  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts

    union order by

    data about currencyies
    Code:
    OTHER1    null
    USD         2
    EUR         1
    OTHER2    null  
    OTHER3    null
    .........
    the desired result

    Code:
    EUR         1
    USD         2
    OTHER1    null
    OTHER2    null  
    OTHER3    null
    .........
    this does not work:
    Code:
    select col1,col2 from tab where col2 is not null order by col2
    union
    select col1,col2 from tab where col2 is null order by col1
    any suggestion ?
    Last edited by BubikolRamios; 02-16-2009 at 11:44 AM.

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    what result did you get?

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    no result coz it is illegal to use order by inside union, can be only at the end.

    the solution is invention of new field
    Code:
    select col1,col2,1 as z_field from tab where col2 is not null
    union
    select col1,col2,2 as z_field  from tab where col2 is null 
    order by z_field,col2,col1

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    why do you need a union at all?

    look into the use of ORDER BY FIELD

  • #5
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    coz it wouldnt work, example:

    Code:
    EUR        2
    USD        1
    other1 null
    order by col2,col1 --> would bring null on top,
    and if I go then with by col2 desc, col1 --> not good either

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    But there is an easy solution for that:
    Code:
    select col1, col2 FROM table 
    order by IF(col2 IS NULL, 2, 1 ), col2, col1
    That's doing essentially the same thing your union-with-added-field is doing but without the need for the added field or the union.
    Last edited by Old Pedant; 02-15-2009 at 10:28 PM.

  • Users who have thanked Old Pedant for this post:

    BubikolRamios (02-16-2009)

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by BubikolRamios View Post
    coz it wouldnt work
    yes it would work without the union, you just don't know how. As I said search on ORDER BY FIELD which is your solution and doesn't need a UNION.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    I think he understood ORDER BY FIELD. He showed that by his use of the z_field in the ORDER BY involving a UNION. What he didn't consider was putting a conditional (e.g., IF or CASE) in the ORDER BY.


  •  

    Posting Permissions

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