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
