mysql union statement with order by

Question

Hi all,

I'am trying to understand what causes the following, maybe you could help me:

I have a query like:

 select field1,fieldDate from table1 
 union all 
 select field1,fieldDate from table2
 order by fieldDate desc

and the another one like this:

 select field1,field2,fieldDate from table1 
 union all 
 select field1,field2,fieldDate from table2 
 order by fieldDate desc

So basically they are the same with the exception that in the second I retrieve an extra field.

Now, both results come with a diferent ordering, but just for the cases that the dates are exacly the same. For example there are 2 rows (row1,row2) with date 2009-11-25 09:41:55. For query 1 row1 comes before row2 and for query 2 row2 comes before row1. Does somebody knows why this happens?

Thanks, Regards

Answer 1

EDIT: This answer is wrong: the order by works on the entire union. I'll leave it here to save others the trouble :)

---

Your order by only works on the second part of the union. You can use a subquery to make the order by work on the entire union:

select field1,field2,fieldDate 
from (
    select field1,field2,fieldDate 
    from table1 
    union all 
    select field1,field2,fieldDate 
    from table2
) SubQueryName
order by fieldDate desc

Answer 2

Can you do this

  select * from ( select 
     field1,field2,fieldDate, 0 as ordercol from table1 
    union all select 
     field1,field2,fieldDate, 1 as ordercol from table2) t1
    order by fieldDate desc, ordercol asc

Answer 3

The ordering based on any fields that you don't explicitly order by is undefined, and the optimizer can change the ordering if it thinks that results in a better execution plan. Given two rows with the exact same value in the order by field you can not depend on them being in any particularly order in relation to each other unless you explicitly order by another field with different values.

Answer 4

Straight from the MySQl manual, to user order by on a union you have to parenthesis the individual tables.

(select field1,fieldDate from table1)
union all 
(select field1,fieldDate from table2)
order by fieldDate desc

This is not SQL standards compliant! The code you entered should order the union of both tables but to my surprise MySQL has the above syntax.

The order in which rows with the same fieldDate are returned can differ for each query execution. Usually this order will be the same but you should not count on it. If you want any extra ordering state more order by fields.