order by/union all

[Tiger66]

Hi I am just wondering can I use order by and union all together?

When I try the statement below
select ......... order by ID
union all
select ......... order by ID

I get an invalid SQL statement error message

If I can't use order by/union together, are there any workaround?

Thanks

[ultraslacker]

Remove the first ORDER BY clause.

[Sxooter]

The proper way to do this is with with subselects and the order by on the outside like so:

select 
 * 
from (
  select * from table1
    union 
  select * from multi
) as s1 
order by s1.i1;