ORDER BY clause and function with alias column name in SQL Server

ORDER BY clause is really tricky sometime.  It doesn’t accept alias column name in some situation nor it is being used in Subquery. However we do have some alternate way with the trick to do so.

There was small discussion going about this on Vijaya’s Blog Where I have participated.  .NET MVP Vijaya’s raised the question about this topic and I found it interesting that is why I am sharing it here.

Basically he asked readers that why Order By is working with column alias

For eg:

SELECT

LoginID,

HireDate,

GETDATE() AS Current_Dt

FROM HumanResources.Employee

ORDER BY Current_Dt

And why the same alias doesn’t work with the function in ORDER BY clause.

For eg:

SELECT

LoginID,

HireDate,

GETDATE() AS Current_Dt

FROM HumanResources.Employee

ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));

I found one trick with CTE which can work for this:

For Eg:

with cte as

(

SELECT

LoginID,

HireDate,

GETDATE() AS Current_Dt

FROM HumanResources.Employee

)select loginid,hiredate,current_dt from cte

ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate))

I would like my blog reader to participate there. Here is the link for that article. DotNetVJ

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com