Today, i came across a strange behaviour of ORDER BY clause in SQL Server. In one of my post i mentioned that ORDER BY clause is final step in the SQL Query if you don't have TOP keyword in the query.
Lets look at the below example:
SELECT
BusinessEntityID,
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY Current_Dt
In this query i have an alias name in the ORDER BY Clause and SQL Server executes the query with out any errors as shown below:
Now If i use any functions on the alias column in the ORDER BY clause, it comes out with errors:
SELECT BusinessEntityID,
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));
Do let me know if you have any answer to this question....
Reference : Vijaya Kadiyala (www.DotNetVJ.com)
7 comments:
Really very good point here.
This is the reason for logical query execution plan.
I have written about that earlier but again good thing you pointed out.
http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/
Kind Regards,
Pinal
Because you are running the query in SQL 2008...
One of the most annoying limitations of T-SQL is the restriction of column alias to be only used in the ORDER BY clause. It cannot be reused in SELECT, cannot be used in WHERE or GROUP BY.
but you could do something like this:
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))
BTW, this is really go observation. :)
I will suggest the alternative should be something like
SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt,
ABS(DATEDIFF(DAY,GETDATE(),HireDate)) NewCol
FROM HumanResources.Employee
ORDER BY NewCol
Thanks pinal..thats another solution.. :)
You may want to read one of my blog entries where I talk about something very much like this:
http://bradsruminations.blogspot.com/2010/01/trolls-puzzle-sql-fable.html
--Brad
Post a Comment