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.
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