Pages

▼

Wednesday, January 20, 2010

Order By Clause is not accepting alias Name in SQL Server

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)

at

7 comments:

  1. Pinal DaveJanuary 22, 2010 at 5:25 AM

    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

    ReplyDelete
  2. AnonymousJanuary 24, 2010 at 12:21 PM

    Because you are running the query in SQL 2008...

    ReplyDelete
  3. Ritesh ShahJanuary 25, 2010 at 9:02 AM

    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))

    ReplyDelete
  4. Ritesh ShahJanuary 25, 2010 at 9:05 AM

    BTW, this is really go observation. :)

    ReplyDelete
  5. Pinal DaveJanuary 27, 2010 at 4:30 AM

    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

    ReplyDelete
  6. Vijaya KadiyalaJanuary 27, 2010 at 9:28 AM

    Thanks pinal..thats another solution.. :)

    ReplyDelete
  7. Brad SchulzFebruary 1, 2010 at 3:06 PM

    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

    ReplyDelete