MYSQL UNION ORDERING

Is it possible to order the first enquiry and keep the rows returned as first and not order the second enquiry. (If that makes sence) An example of my current enquiry is : SELECT

Solution 1:

You'd need to introduce an artificial sort key. Something like:

SELECT
    *, 1 as SortKey
FROM 
    Devices
WHERE
    Live = 'true'
    AND Category = 'apple'
UNION
SELECT
        *, 2 as SortKey
    FROM
        Devices
    WHERE
        DeviceLive = 'true'
ORDER BY SortKey, ListOrder

Solution 2:

UNION has an implicit de-duplication function. This means that a side effect of using it is everything gets jumbled together as its sorted for that to happen. It also (usually, but not always) means that you get sorted output (see next paragraph).

UNION ALL removes this, but, there's still the caveat that output from a query without an explicit order by has no guaranteed ordering. This has been covered by another answer, but I thought it worthwhile to point out why it matters.

Solution 3:

You could fake it

SELECT col1, col2 ... 
FROM (
  SELECT 1 fake_order, col1, col2 ... FROM Devices WHERE Live ='true' AND Category='apple' ORDER BY ListOrder
  UNION ALL
  SELECT 2, col1, col2 ... FROM Devices WHERE DeviceLive ='true'
) AS T 
ORDER BY fake_order

(Untested SQL off the top of my head)

Solution 4:

I got my answer from Joe Stefanelli, but modified it a bit:

hi, i didn't need to make an artificial sort key, I just made sure that each table both had the same name of the column i wanted to sort on. In my case, the column name was "order". So I did:

Select *, order as uOrder from table 1 
UNION 
select *, order as uOrder from table 2 
ORDER BY uOrder