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

Thanks, will try that out and report back.
I started to use the union instead of the OR when we
were still on Interbase and the difference in speed then was enormous. We then
moved to Firebird and the difference was still there.
Note that we are still on Firebird 1.5 if that makes a difference.

RBS

On 7/16/12, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>>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
>