The VFP help is more clear on the subject "Only the final SELECT can have an ORDER BY clause, which must refer to output columns by number. If an ORDER BY clause is included, it affects the entire result."

>Sergey,
>
>Thanks, that is very helpful as I will have a lot more control now<s>!
>
>Terry
>
>>Terry,
>>
>>It has nothing to do with identity field.
>>According to BOL, there can be only one ORDER BY clause per statement at the very end of it

select * from trnhead where shipped=0
>>union all
>>select * from ordhead where shipped=0 order by id

>>
>>>id field is primary key w/1 increment identity on that field as well.
>>>
>>>I can do:
>>>select * from trnhead where shipped=0 order by id
>>>fine.
>>>
>>>but:
>>>select * from trnhead where shipped=0 order by id
>>>union all
>>>select * from ordhead where shipped=0 order by id
>>>won't work with the order bys in there.
>>>
>>>I just took the order bys out since the ordering is okay it seems without, but:
>>>
>>>Does anyone know why?