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
Advertisements

6 Responses to “ORDER BY clause and function with alias column name in SQL Server”

  1. Vijaya Kadiyala Says:

    probably we should raise this to Microsoft SQL Server team to comment on this…btw now I am a SQL Server MVP now 😉 i was .Net MVP in 2008 and 2009. I am sure you will be in the list very shortly..

  2. Vijaya Kadiyala Says:

    probably we should raise this to Microsoft SQL Server team to comment on this…btw now I am a SQL Server MVP now 😉 i was .Net MVP in 2008 and 2009. I am sure you will be in the list very shortly..

  3. Ritesh Shah Says:

    Hi Vijaya, That is great. many congratulations to you.

  4. Ritesh Shah Says:

    Hi Vijaya, That is great. many congratulations to you.

  5. Madhivanan Says:

    It is becuase when you use function over alias name, it has to know the column in advance just like WHERE clause. But when you dont use a function, as ORDER BY is performed last,it knows the new column nameThe actual workaround should be a usage of derived table which will work in all the versionsselect * from(SELECTLoginID,HireDate,GETDATE() AS Current_DtFROM HumanResources.Employee) as tORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));ORDER BY Current_DtMadhivananhttp://beyondrelational.com/blogs/madhivanan

  6. Madhivanan Says:

    It is becuase when you use function over alias name, it has to know the column in advance just like WHERE clause. But when you dont use a function, as ORDER BY is performed last,it knows the new column nameThe actual workaround should be a usage of derived table which will work in all the versionsselect * from(SELECTLoginID,HireDate,GETDATE() AS Current_DtFROM HumanResources.Employee) as tORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));ORDER BY Current_DtMadhivananhttp://beyondrelational.com/blogs/madhivanan


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: