Using calculation with an an aliased column in ORDER BY

As we all know, the ORDER BY clause is processed after the SELECT clause, so a column alias in the SELECT clause can be used.

However, I find that I can’t use the aliased column in a calculation in the ORDER BY clause.

WITH data AS(
    SELECT *
    FROM (VALUES
        ('apple'),
        ('banana'),
        ('cherry'),
        ('date')
    ) AS x(item)
)
SELECT item AS s
FROM data
--  ORDER BY s;         -- OK
--  ORDER BY item + ''; -- OK
ORDER BY s + '';        -- Fails

I know there are alternative ways of doing this particular query, and I know that this is a trivial calculation, but I’m interested in why the column alias doesn’t work when in a calculation.

I have tested in PostgreSQL, MariaDB, SQLite and Oracle, and it works as expected. SQL Server appears to be the odd one out.

Answer

The documentation clearly states that:

The column names referenced in the ORDER BY clause must correspond to either a column or column alias in the select list or to a column defined in a table specified in the FROM clause without any ambiguities. If the ORDER BY clause references a column alias from the select list, the column alias must be used standalone, and not as a part of some expression in ORDER BY clause:

Technically speaking, your query should work since order by clause is logically evaluated after select clause and it should have access to all expressions declared in select clause. But without looking at having access to the SQL specs I cannot comment whether it is a limitation of SQL Server or the other RDBMS implementing it as a bonus feature.

Anyway, you can use CROSS APPLY as a trick…. it is part of FROM clause so the expressions should be available in all subsequent clauses:

SELECT item
FROM t
CROSS APPLY (SELECT item + '') AS CA(item_for_sort)
ORDER BY item_for_sort