Alex Rivera | Logout

MYSQL UNION ORDERING

Asked 2010-11-24T14:10:05.780
9

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
    *
FROM 
    Devices
WHERE
    Live = 'true'
    AND Category = 'apple'
ORDER BY
    ListOrder
UNION
SELECT
        *
    FROM
        Devices
    WHERE
        DeviceLive = 'true'

I was hoping that the devices under the category apple would be organised in there list order and would be at the top of the list above the other devices. But this seems to jumble the two querys together.

sql mysql
Edit
Report

2 Answers

5

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.

answered 2010-11-24T14:22:13.737
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)

answered 2010-11-24T14:16:16.350

Your Answer