Hello Set,
> > 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. > Having a closer look, it appears that the resultset of the non-union query is not properly sorted. > 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). > I think your solution should work. I found another solution that does work without the need of an extra column in the select clause: "select "TESTID", "NAME" from test union select "TESTID", "NAME" from test order by 2 COLLATE UNICODE_CI" Thank you for your effort, Roy
