T-SQL: Troubleshooting When a Column Alias Created in the SELECT Clause Cannot be Used in the ORDER BY Clause

아티클
1/17/2024

DOWNLOAD
the
CODE

All Codes used in this article is downloadable from this URL.

** **

Introduction

This article’s aims to demonstrate a known issue in SQL query execution, and two general workarounds to solve this issue. Although this issue has at least two workarounds, it is a good idea to vote for a fix in the above URL. Also, please add your ideas about this issue in the comments.

Problem

As mentioned in this BOL content, we can find the “Logical Processing Order of the SELECT statement” which is a very important concept in query execution. The next paragraph quotes from this section:

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

We cannot always use the column aliases in the ORDER BY clause. For example, when we want to use a column alias in the CASE expression or convert it to other data type (for sort purposes) or even use it within an expression. The following sample shows this:

Code 1

--create sample table
IF OBJECT_ID('dbo.Letters', 'U') IS NOT NULL
    DROP TABLE  dbo.Letters;
CREATE TABLE  dbo.Letters
    (
      LetterID INT  IDENTITY
                   PRIMARY KEY  ,
      IndicatorCode NVARCHAR(20) ,
      LetterType TINYINT ,
      Title NVARCHAR(500)
    )
GO
 
--insert sample data
INSERT  dbo.Letters
        ( IndicatorCode, LetterType, Title )
VALUES  ( N'2004/9/abc/2' , 1, N'Letter 9' ) ,
        ( N'2004/10/abc/2', 1, N'Letter 10' ) ,
        ( N'2004/10/zzz/2', 1, N'Letter 11' ) 
GO
 
--query will fail
SELECT  * , 
    PARSENAME(REPLACE(IndicatorCode, N'/', N'.'), 3)  AS  [Second  Part]
FROM    dbo.Letters
ORDER BY
    CAST([Second Part] AS INT), 
    [Second Part];

As illustrated in the picture_01, the error message occurs because of using the alias in the first calling. If we use it alone, like the second line of the Order By clause, it works.

picture_01

Solutions

Solution One – Duplicate Computation Column

The first workaround for this problem is to duplicate the code. Instead of using its alias, we can use the same code that we named it with a new alias. We can change the above code to this one:

Code 2

--workaround 1
SELECT  * , 
    PARSENAME(REPLACE(IndicatorCode, N'/', N'.'), 3)  AS  [Second  Part]
FROM    dbo.Letters
ORDER BY
    CAST(PARSENAME(REPLACE(IndicatorCode, N'/', N'.'), 3) AS  INT), 
    [Second Part];

picture_02

The whole code and the result is showed in the picture_02. In this code, we just duplicated the code instead of using the alias in the first column in the ORDER BY clause. The second point is that we refer the alias whenever we can. This is why we used the alias in the second line of the ORDER BY clause.

Solution Two – Using CROSS APPLY

The second workaround for this problem is using the CROSS APPLY instead of duplicating code. This way is the cleanest solution we can use and perhaps safest since if this issue is eventually "fixed" we might find duplicating aliases an issue. We can instead:

Code 3

--workaround 2
SELECT  * , 
     S1.[Second Part]
FROM    dbo.Letters AS L
CROSS APPLY 
    ( SELECT  PARSENAME(REPLACE(IndicatorCode, N'/', N'.'), 3) AS  [Second  Part]) AS  S1
ORDER BY
    CAST(S1.[Second Part] AS INT), 
    S1.[Second Part];

As illustrated in the following picture, in this solution, we use the computation column code in the CROSS APPLY phase. Then, we can use it in the SELECT clause and also in the ORDER BY clause without any problem. This is a much cleaner technique than duplicating code and therefore likely to be the preferred choice.

picture_03

Conclusion

This article shows a known issue in SQL query execution, and two general workarounds to solve this issue. There are other solutions like using Views or Sub Queries. But, these two solutions are easy to use and the second one is the cleanest one.