On 28-6-2012 9:21, Svein Erling Tysvær wrote:

>> Maybe I am wrong but I always thought the "order by" is applied on the
>> results of the two selects in both the second and third union query.
>> And the order by clause does work in the second query (also an union
>> query) "order by 2" and does not work in the third query "order by
>> upper(2)". The only difference is the use of "upper()" which does work
>> with the first query with only one select.
>
> This puzzles me, although I'm uncertain whether I'm puzzled by upper(2) not working with UNION or puzzled by upper(2) working without UNION.
>
> Here, I first wrote that it works when using a CTE, but then I discovered that it only works in the sense that it produces a result set, the ordering seems random. At least, the following simple SQL produces incorrect ordering:
>
> Select rdb$collation_name
>>From rdb$collations
> Order by upper(1)
>
> This on Firebird 2.5.1.
>
> Hence, I think what you want to order by must be selected, i.e.
>
> With tmp as
> (select "TESTID", "NAME", upper(name) as MyOrdering from test
> union
> select "TESTID", "NAME", upper(name) from test order by 3)
>
> select "TESTID", "NAME"
> from tmp
>
> (I haven't tested this last query, just expects it to be returned correctly ordered).

That is to be expected. Ordering by UPPER(2) does not sort by the second
column, but it will do the following:
=> Coerce 2 to "2",
=> Execute UPPER("2") which results in "2".
As all rows will be ordered by "2" they are not ordered at all (they are
simply returned in the original order).

Mark
--
Mark Rotteveel