Using calculation with an an aliased column in ORDER BY-CodePudding

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

CodePudding user response：

It is simply due to the way expressions are evaluated. A more illustrative example:

;WITH data AS
(
   SELECT * FROM (VALUES('apple'),('banana')) AS sq(item)
)
SELECT item AS s
  FROM data
  ORDER BY CASE WHEN 1 = 1 THEN s END;

This returns the same Invalid column name error. The CASE expression (and the concatenation of s '' in the simpler case) is evaluated before the alias in the select list is resolved.

One workaround for your simpler case is to append the empty string in the select list:

SELECT
  item   '' AS s
...
ORDER BY s;

There are more complex ways, like using a derived table or CTE:

;WITH data AS
(
  SELECT * FROM (VALUES('apple'),('banana') AS sq(item)
),
step2 AS
(
  SELECT item AS s FROM data
)
SELECT s FROM step2 ORDER BY s '';

This is just the way that SQL Server works, and I think you could say "well SQL Server is bad because of this" but SQL Server could also say "what the heck is this use case?" :-)