Behavior of order by When Identical Table Column Name and Column Alias Name Exist

The SAP ASE server interprets a column name in the order by clause as the alias name under several specific conditions.

The order by clause contains a reference to a qualified column name (that is, order by table.column).
Both the table column name and alias name exist.
The names for both the table column and alias are identical to the column name in the order by clause.

In this example, the result set from the two queries differ, even though the order by clause is identical; yet, the order by clause refers to a different column in both cases.

create table t (A int, B char(3))
insert into t (A, B) values(1, 'az')
insert into t (A, B) values(2, 'bb')
go
/* t.B refers to the table column B */
select A, reverse(B) as C from t order by t.B
go
/* t.B refers to the alias column B */
select A, reverse(B) as B from t order by t.B
go

A C
----------- ---
1 za
2 bb

(2 rows affected)

A B
----------- ---
2 bb
1 za

(2 rows affected)

This behavior occurs because the SAP ASE server allows the order by clause to reference an alias column name that is qualified by the table name. When a base table column also exists with the same colum name as the alias column, the SAP ASE server gives precedence to the alias column.