I was reading Markus Winand’s latest post on ORDER BY history last week. If you haven’t read it yet, go read it. Markus is one of the best writers on SQL standards, and this post is no exception.
One line stopped me cold. The compatibility table for “expressions on selected columns.” Postgres: partial. PostgreSQL 18: still partial.
That itch needed scratching.
The basic version of this is that you cannot use the alias of a computed expression in a function in the ORDER BY clause in either PostgreSQL or SQL Server. In other words, the following fails:
SELECT a + b AS x
FROM t
ORDER BY COALESCE(x, 0);
Read on for an explanation of why this is the case in PostgreSQL. I’d imagine that the reasoning is about the same for SQL Server.