>Firebird 1.5 classic on Windows.
>Is it possible to have a SQL with union all and an order by that includes a field that is not in the selects?

I'd be very surprised if this was possible, logically you sort a result set and the result set that does not contain your field. If it wasn't for the union, it is at least thinkable (for people like me without knowledge of Firebird internals) that the sorting could be done during the selection of records and that it could sort on the field. Though it should be simple for you to test to find out whether it is possible or not.

>In case you wondered why I used a union here, it is as I found it be a lot faster than using an OR.

That must either be coincidental or because the PLAN changes. My guess is that your selectivity indicates that using an index is marginally better than using NATURAL, but that this changes once you introduce OR (it isn't generally quicker to use UNION than OR). Since Firebird doesn't have histograms yet (it doesn't know whether 0.001% or 99% starts with 'bd' or 'bi'), this may be a wrong choice.

If my guess is correct, then the following might make your query quick again:

select fieldA, fieldB from table1
where (fieldA starting with 'bd'
or fieldA starting with 'bi')
and fieldA between 'bd' and 'bj')
order by fieldA asc, fieldC asc

HTH,
Set