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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2009
    Posts
    147
    Thanks
    3
    Thanked 22 Times in 22 Posts

    ORDER BY in Union

    I am struggling to order this mysql query correctly

    Code:
    SELECT G.the_location, SUM(G.total_cost) AS mytotal, G.the_name
    FROM
    (SELECT A.hp_location AS the_location, C.cost AS total_cost, XX.full_name AS the_name
    FROM {$sql_instance}register AS A 
    INNER JOIN {$sql_instance}register_items AS B ON A.order_id = B.order_id 
    INNER JOIN {$sql_instance}description AS C ON B.item_id = C.id_item 
    INNER JOIN {$sql_instance}avaliable AS XX ON A.hp_location = XX.hp_location
    WHERE $the_location C.min_age >= '$min_age' AND C.max_age <= '$max_age' AND C.date >= '$start' AND C.date <= '$end' AND A.pay_status != '1'
    UNION ALL 
    SELECT Z.hp_location AS the_location, E.a_cost AS total_cost, XZ.full_name AS the_name
    FROM {$sql_instance}register AS Z 
    INNER JOIN {$sql_instance}register_items AS Y ON Z.order_id = Y.order_id 
    INNER JOIN {$sql_instance}description AS X ON Y.item_id = X.id_item 
    INNER JOIN {$sql_instance}register_attributes AS D ON Y.item_id = D.product_id AND Y.order_id = D.order_id
    INNER JOIN {$sql_instance}attributes_description AS E ON D.attribute_id = E.attribute_id
    INNER JOIN {$sql_instance}avaliable AS XZ ON Z.hp_location = XZ.hp_location
    WHERE $the_location2 X.min_age >= '$min_age' AND X.max_age <= '$max_age' AND X.date >= '$start' AND X.date <= '$end' AND Z.pay_status != '1') AS G
    GROUP BY G.the_location
    ORDER BY G.the_location
    Currently it is ordering like so:

    ['Starting Monday, 5 August 2013 - 11', 30.00], ['Starting Monday, 12 August 2013 - 12', 131.00], ['Starting Monday, 10 June 2013 - 3', 20.00]

    After each date you can see a number, 11, 12, 3 which is the alias the_location

    I assume its ordering each union seperatley, as opposed to as one, given they have the same alias I am confused++++

    How do I have the query order as a whole, ie. 3, 11, 12

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Start by showing us the *ACTUAL* query used.

    That is, get rid of all the PHP variables in there and show us the query that is actually being passed to MySQL.

    And show use the *ACTUAL* output from the query, *NOT* whatever your PHP code is doing to it.
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Also, what is the DATA TYPE of the field register.hp_location? Is it a number? INT? Or is it VARCHAR?

    If it is VARCHAR, then the ordering makes sense: "11" is less than "12" which is less than "3". ONLY if the field is a NUMBER would the ordering be 3, 11, 12.
    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.

  • #4
    Regular Coder
    Join Date
    Jun 2009
    Posts
    147
    Thanks
    3
    Thanked 22 Times in 22 Posts
    Hit the nail on the head, the field is a varchar damit! Will change it around. Thanks very much!

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    You don't have to change it. You can just convert it in the ORDER BY.

    Code:
    ...
    ORDER BY CAST( G.the_location AS UNSIGNED )
    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
    •